

-- ----------------------------
-- Table structure for [Activities]
-- ----------------------------
DROP TABLE [Activities]
GO
CREATE TABLE [Activities] (
[SessionId] varchar(36) NOT NULL ,
[UserId] int NOT NULL ,
[ForumId] int NOT NULL ,
[TopicId] int NOT NULL ,
[Login] datetime NOT NULL ,
[LastAction] datetime NOT NULL ,
[UserName] varchar(64) NULL ,
[IP] varchar(20) NOT NULL ,
[PageTitle] nvarchar(250) NOT NULL ,
[Location] nvarchar(250) NOT NULL ,
[Browser] nvarchar(50) NULL ,
[Platform] nvarchar(50) NULL ,
[IsPrivated] bit NOT NULL 
)


GO

-- ----------------------------
-- Records of Activities
-- ----------------------------
BEGIN TRANSACTION
GO
INSERT INTO [Activities] ([SessionId], [UserId], [ForumId], [TopicId], [Login], [LastAction], [UserName], [IP], [PageTitle], [Location], [Browser], [Platform], [IsPrivated]) VALUES (N'0cfcfec3-4fa9-4205-a3e6-5b3625d7856f', N'0', N'0', N'0', N'2011-11-10 11:04:30.300', N'2011-11-10 11:04:30.300', N'', N'127.0.0.1', N'Register', N'/Member/Register/', N'Firefox 8.0', N'Se7en', N'0');
GO
COMMIT TRANSACTION
GO

-- ----------------------------
-- Table structure for [AttachFiles]
-- ----------------------------
DROP TABLE [AttachFiles]
GO
CREATE TABLE [AttachFiles] (
[Id] int NOT NULL IDENTITY(1,1) ,
[TopicId] int NOT NULL ,
[Name] nvarchar(100) NULL ,
[Path] varchar(50) NOT NULL ,
[Created] datetime NOT NULL ,
[Downloads] int NOT NULL 
)


GO

-- ----------------------------
-- Records of AttachFiles
-- ----------------------------
BEGIN TRANSACTION
GO
SET IDENTITY_INSERT [AttachFiles] ON
GO
SET IDENTITY_INSERT [AttachFiles] OFF
GO
COMMIT TRANSACTION
GO

-- ----------------------------
-- Table structure for [EmailQueue]
-- ----------------------------
DROP TABLE [EmailQueue]
GO
CREATE TABLE [EmailQueue] (
[Id] int NOT NULL IDENTITY(1,1) ,
[From] varchar(64) NOT NULL ,
[To] varchar(64) NOT NULL ,
[Subject] nvarchar(100) NOT NULL ,
[Body] ntext NOT NULL ,
[Created] datetime NOT NULL ,
[Sended] datetime NULL ,
[Priority] smallint NOT NULL 
)


GO
DBCC CHECKIDENT(N'[EmailQueue]', RESEED, 209)
GO

-- ----------------------------
-- Records of EmailQueue
-- ----------------------------
BEGIN TRANSACTION
GO
SET IDENTITY_INSERT [EmailQueue] ON
GO
SET IDENTITY_INSERT [EmailQueue] OFF
GO
COMMIT TRANSACTION
GO

-- ----------------------------
-- Table structure for [Forums]
-- ----------------------------
DROP TABLE [Forums]
GO
CREATE TABLE [Forums] (
[ForumId] int NOT NULL IDENTITY(1,1) ,
[ParentId] int NULL DEFAULT ((0)) ,
[IsLocked] bit NOT NULL DEFAULT ((0)) ,
[AllowPostWithIn] bit NOT NULL ,
[VisibleAtHomePage] bit NOT NULL DEFAULT ((1)) ,
[IncreaseMemberPosts] bit NOT NULL DEFAULT ((1)) ,
[Icon] varchar(150) NULL ,
[ForumName] nvarchar(250) NULL ,
[Keywords] nvarchar(150) NULL ,
[Description] nvarchar(200) NULL ,
[SortOrder] smallint NOT NULL DEFAULT ((1)) ,
[RemoteUrl] nvarchar(256) NULL ,
[ModerateType] smallint NOT NULL DEFAULT ((0)) ,
[AllowRoles] varchar(100) NULL ,
[TotalTopics] bigint NOT NULL DEFAULT ((0)) ,
[TotalReplies] bigint NOT NULL DEFAULT ((0)) ,
[OrderBy] tinyint NOT NULL ,
[SortDirection] tinyint NOT NULL ,
[TopicsPerPage] int NOT NULL DEFAULT ((25)) ,
[PostsPerPage] int NOT NULL DEFAULT ((25)) ,
[AllowSearch] bit NOT NULL DEFAULT ((1)) ,
[LastPostId] int NULL ,
[LastPostTitle] nvarchar(250) NULL ,
[LastPostDate] datetime NULL ,
[LastUserPost] varchar(64) NULL 
)


GO
DBCC CHECKIDENT(N'[Forums]', RESEED, 58)
GO

-- ----------------------------
-- Records of Forums
-- ----------------------------
BEGIN TRANSACTION
GO
SET IDENTITY_INSERT [Forums] ON
GO
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'1', N'0', N'0', N'0', N'1', N'1', null, N'Programing', N'Web development', N'Web development', N'1', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'25', N'0', null, null, null, null);
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'2', N'1', N'0', N'1', N'1', N'1', null, N'Javascript', N'jquery, JQuery plugins,mootool, prototype js', N'Share all aspects of programming Javascript and Javascript Framework', N'1', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'25', N'0', null, null, null, null);
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'3', N'1', N'0', N'1', N'1', N'1', null, N'HTML & CSS', N'HTML,CSS framework', null, N'2', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'25', N'0', null, null, null, null);
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'5', N'1', N'0', N'1', N'1', N'1', null, N'Flash & ActionScript', N'Flash & ActionScript
', N'Learning, exchange of experience in design and ActionScript Flash Effect', N'5', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'25', N'0', null, null, null, null);
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'6', N'7', N'0', N'1', N'1', N'1', null, N'Silverlight', N'Silverlight', N'', N'5', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'25', N'1', null, null, null, null);
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'7', N'1', N'0', N'1', N'1', N'1', null, N'.Net Framework', N'.Net Framework', null, N'4', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'25', N'0', null, null, null, null);
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'8', N'7', N'0', N'1', N'1', N'1', null, N'ASP.NET', N'', N'', N'1', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'25', N'1', null, null, null, null);
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'9', N'7', N'0', N'1', N'1', N'1', null, N'XML & Web Services', N'XML & Web Services', N'', N'2', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'25', N'1', null, null, null, null);
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'10', N'1', N'0', N'1', N'1', N'1', null, N'Asp.Net Mvc Framework', N'asp.net mvc, mvc framework', N'This forum will help you conquer model that all web programmers have to work towards.', N'3', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'25', N'0', null, null, null, null);
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'11', N'7', N'0', N'1', N'1', N'1', null, N'LINQ', N'LINQ', N'', N'4', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'25', N'1', null, null, null, null);
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'12', N'7', N'0', N'1', N'1', N'1', null, N'ADO.NET', N'ADO.NET', N'Câu hỏi hoặc câu trả lời về các chủ đề liên quan đến công nghệ ADO.NET', N'6', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'25', N'1', null, null, null, null);
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'18', N'1', N'0', N'1', N'1', N'1', null, N'Test Automation Services', null, N'Testing, software testing, automation applications - helping you develop effective systems to optimize performance, time, money ...', N'9', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'25', N'0', null, null, null, null);
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'19', N'0', N'0', N'0', N'1', N'1', null, N'CSDL - Databases', N'phan tich, thiet ke, quan trị csdl, database, sql, mysql,oracle', N'Thảo luận các vấn đề liên quan đến phân tích, thiết kế các hệ quản trị CSDL', N'4', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'25', N'1', null, null, null, null);
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'21', N'19', N'0', N'1', N'1', N'1', null, N'MS SQL', N'', N'', N'1', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'25', N'1', null, null, null, null);
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'22', N'19', N'0', N'1', N'1', N'1', null, N'Oracle', N'', N'', N'2', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'25', N'1', null, null, null, null);
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'23', N'19', N'0', N'1', N'1', N'1', null, N'MySql', N'', N'', N'3', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'25', N'1', null, null, null, null);
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'24', N'19', N'0', N'1', N'1', N'1', null, N'Other Database', N'Access, SQLite, DB2, VistaDB, PostgreSQL, No SQL', N'Access, SQLite, DB2, VistaDB, PostgreSQL, No SQL....', N'4', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'25', N'0', null, null, null, null);
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'25', N'28', N'0', N'1', N'1', N'1', null, N'Request', null, N'You''re looking for the source code, software, e-books, tutorials ...? Let the forum can help you', N'10', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'25', N'0', null, null, null, null);
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'26', N'1', N'0', N'1', N'1', N'1', null, N'Tips - Tricks', N'thu thuat lap trinh, bao mat, security, code optimize, performance', N'Optimized code, improving performance, security and application development experience ...', N'99', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'25', N'0', null, null, null, null);
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'28', N'0', N'0', N'0', N'1', N'1', null, N'Sharing', N'ebook,tutorial,training,learning,telerik,developer express', N'Chia sẻ E-books, Tuts, mã nguồn dự án...', N'5', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'25', N'0', null, null, null, null);
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'29', N'28', N'0', N'1', N'1', N'1', null, N'Scripts, Sources code, Components', N'componentart, telerik,,DXperience, developer express, devexpress,  scripts,source code, .net component,', null, N'1', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'25', N'0', null, null, null, null);
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'30', N'28', N'0', N'1', N'1', N'1', null, N'Software', null, null, N'2', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'25', N'0', null, null, null, null);
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'32', N'28', N'0', N'1', N'1', N'1', null, N'Web - Templates', N'TemplateMonster, TemplateBox, BoxArt, ThemeStock, RocketTheme, YooTheme', N'TemplateMonster, TemplateBox, BoxArt, ThemeStock, RocketTheme, YooTheme...', N'3', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'25', N'1', null, null, null, null);
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'37', N'28', N'0', N'1', N'1', N'1', null, N'Flash Script - Flash Components', N'activeden,faslhxml,flashloaded,jumpeyecomponents,afcomponents,flashmint', N'JumpeyeComponents, FlashLoaded, FlashMint, FlashXml, Afcomponents...', N'4', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'25', N'1', null, null, null, null);
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'38', N'28', N'0', N'1', N'1', N'1', null, N'Ebooks - Tutorials', null, null, N'5', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'25', N'0', null, null, null, null);
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'39', N'28', N'0', N'1', N'1', N'1', null, N'Graphics - Icons - Vectors', N'', N'Những hình ảnh, Icons, Vectors phục vụ cho thiết kế đồ họa, web hoặc phần mềm. IconShock, iStock, VitaminD, ShutterStock...', N'6', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'25', N'1', null, null, null, null);
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'45', N'1', N'0', N'1', N'1', N'1', null, N'Flex & Flash Builder', N'flex flash builder actionscript Android BlackBerry Tablet
', N'Is a powerful mobile application development Android ™, BlackBerry ®, Tablet, applications and traditional desktop browser.', N'8', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'25', N'0', null, null, null, null);
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'51', N'1', N'0', N'1', N'1', N'1', null, N'Open source projects', N'open source', N'About sharing open source projects useful', N'15', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'25', N'0', null, null, null, null);
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'53', N'0', N'0', N'0', N'1', N'1', null, N'Welcome MvcForum.Net', null, null, N'0', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'25', N'0', null, null, null, null);
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'54', N'53', N'0', N'1', N'1', N'1', null, N'Announcement', null, N'The regularly monitor your information here for the latest news on the forum.', N'1', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'25', N'0', null, null, null, null);
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'55', N'53', N'0', N'1', N'1', N'1', null, N'Comments, suggestions of members', null, null, N'2', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'0', N'0', null, null, null, null);
INSERT INTO [Forums] ([ForumId], [ParentId], [IsLocked], [AllowPostWithIn], [VisibleAtHomePage], [IncreaseMemberPosts], [Icon], [ForumName], [Keywords], [Description], [SortOrder], [RemoteUrl], [ModerateType], [AllowRoles], [TotalTopics], [TotalReplies], [OrderBy], [SortDirection], [TopicsPerPage], [PostsPerPage], [AllowSearch], [LastPostId], [LastPostTitle], [LastPostDate], [LastUserPost]) VALUES (N'56', N'53', N'0', N'1', N'1', N'1', null, N'User Guide forum', null, N'If you have problems while using the forum feel free to ask here. All forum members will help you answer!', N'3', null, N'0', null, N'0', N'0', N'0', N'0', N'25', N'25', N'0', null, null, null, null);
GO
SET IDENTITY_INSERT [Forums] OFF
GO
COMMIT TRANSACTION
GO

-- ----------------------------
-- Table structure for [IPLocks]
-- ----------------------------
DROP TABLE [IPLocks]
GO
CREATE TABLE [IPLocks] (
[Id] int NOT NULL IDENTITY(1,1) ,
[Mask] varchar(20) NOT NULL ,
[Reason] nvarchar(250) NULL ,
[Expires] datetime NOT NULL 
)


GO
DBCC CHECKIDENT(N'[IPLocks]', RESEED, 3)
GO

-- ----------------------------
-- Records of IPLocks
-- ----------------------------
BEGIN TRANSACTION
GO
SET IDENTITY_INSERT [IPLocks] ON
GO
SET IDENTITY_INSERT [IPLocks] OFF
GO
COMMIT TRANSACTION
GO

-- ----------------------------
-- Table structure for [Languages]
-- ----------------------------
DROP TABLE [Languages]
GO
CREATE TABLE [Languages] (
[LangId] char(2) NOT NULL ,
[LangName] nvarchar(50) NOT NULL 
)


GO

-- ----------------------------
-- Records of Languages
-- ----------------------------
BEGIN TRANSACTION
GO
COMMIT TRANSACTION
GO

-- ----------------------------
-- Table structure for [Likes]
-- ----------------------------
DROP TABLE [Likes]
GO
CREATE TABLE [Likes] (
[TopicId] int NOT NULL ,
[FromId] int NOT NULL ,
[Created] datetime NOT NULL ,
[ToId] int NOT NULL 
)


GO

-- ----------------------------
-- Records of Likes
-- ----------------------------
BEGIN TRANSACTION
GO
COMMIT TRANSACTION
GO

-- ----------------------------
-- Table structure for [Logs]
-- ----------------------------
DROP TABLE [Logs]
GO
CREATE TABLE [Logs] (
[Id] int NOT NULL IDENTITY(1,1) ,
[Date] datetime NOT NULL ,
[Thread] varchar(255) NOT NULL ,
[Level] varchar(50) NOT NULL ,
[Logger] varchar(255) NOT NULL ,
[Message] varchar(4000) NOT NULL ,
[Exception] varchar(2000) NULL 
)


GO

-- ----------------------------
-- Records of Logs
-- ----------------------------
BEGIN TRANSACTION
GO
SET IDENTITY_INSERT [Logs] ON
GO
SET IDENTITY_INSERT [Logs] OFF
GO
COMMIT TRANSACTION
GO

-- ----------------------------
-- Table structure for [Moderators]
-- ----------------------------
DROP TABLE [Moderators]
GO
CREATE TABLE [Moderators] (
[Id] int NOT NULL IDENTITY(1,1) ,
[UserId] int NOT NULL ,
[UserName] varchar(64) NOT NULL ,
[ForumId] int NOT NULL ,
[AccessMask] varchar(MAX) NOT NULL 
)


GO
DBCC CHECKIDENT(N'[Moderators]', RESEED, 6)
GO

-- ----------------------------
-- Records of Moderators
-- ----------------------------
BEGIN TRANSACTION
GO
SET IDENTITY_INSERT [Moderators] ON
GO
SET IDENTITY_INSERT [Moderators] OFF
GO
COMMIT TRANSACTION
GO

-- ----------------------------
-- Table structure for [Notifications]
-- ----------------------------
DROP TABLE [Notifications]
GO
CREATE TABLE [Notifications] (
[Id] bigint NOT NULL IDENTITY(1,1) ,
[Cmd] tinyint NOT NULL ,
[ForumId] int NULL ,
[TopicId] int NULL ,
[From] int NOT NULL ,
[To] int NOT NULL ,
[Location] nvarchar(200) NULL ,
[Created] datetime NOT NULL 
)


GO
DBCC CHECKIDENT(N'[Notifications]', RESEED, 29)
GO

-- ----------------------------
-- Records of Notifications
-- ----------------------------
BEGIN TRANSACTION
GO
SET IDENTITY_INSERT [Notifications] ON
GO
INSERT INTO [Notifications] ([Id], [Cmd], [ForumId], [TopicId], [From], [To], [Location], [Created]) VALUES (N'23', N'0', N'29', N'-1', N'4', N'0', N'/go/3224/', N'2011-10-11 03:15:30.570');
INSERT INTO [Notifications] ([Id], [Cmd], [ForumId], [TopicId], [From], [To], [Location], [Created]) VALUES (N'24', N'0', N'10', N'-1', N'1', N'0', N'/go/3225/', N'2011-11-02 07:11:13.273');
INSERT INTO [Notifications] ([Id], [Cmd], [ForumId], [TopicId], [From], [To], [Location], [Created]) VALUES (N'25', N'0', N'10', N'-1', N'1', N'0', N'/go/3226/', N'2011-11-02 07:15:09.570');
INSERT INTO [Notifications] ([Id], [Cmd], [ForumId], [TopicId], [From], [To], [Location], [Created]) VALUES (N'26', N'0', N'10', N'-1', N'1', N'0', N'/go/3227/', N'2011-11-02 07:16:48.530');
INSERT INTO [Notifications] ([Id], [Cmd], [ForumId], [TopicId], [From], [To], [Location], [Created]) VALUES (N'27', N'0', N'10', N'-1', N'1', N'0', N'/go/3228/', N'2011-11-02 07:18:39.903');
INSERT INTO [Notifications] ([Id], [Cmd], [ForumId], [TopicId], [From], [To], [Location], [Created]) VALUES (N'28', N'1', N'17', N'3229', N'1', N'0', N'/go/3229/', N'2011-11-02 08:46:02.340');
INSERT INTO [Notifications] ([Id], [Cmd], [ForumId], [TopicId], [From], [To], [Location], [Created]) VALUES (N'29', N'15', null, N'3195', N'1', N'4', N'/go/3195/', N'2011-11-03 04:23:25.163');
GO
SET IDENTITY_INSERT [Notifications] OFF
GO
COMMIT TRANSACTION
GO

-- ----------------------------
-- Table structure for [Polls]
-- ----------------------------
DROP TABLE [Polls]
GO
CREATE TABLE [Polls] (
[PollId] int NOT NULL IDENTITY(1,1) ,
[Answer] nvarchar(MAX) NOT NULL ,
[Expires] datetime NULL ,
[MultipleChoice] bit NOT NULL 
)


GO

-- ----------------------------
-- Records of Polls
-- ----------------------------
BEGIN TRANSACTION
GO
SET IDENTITY_INSERT [Polls] ON
GO
SET IDENTITY_INSERT [Polls] OFF
GO
COMMIT TRANSACTION
GO

-- ----------------------------
-- Table structure for [PrivateMessages]
-- ----------------------------
DROP TABLE [PrivateMessages]
GO
CREATE TABLE [PrivateMessages] (
[PMId] int NOT NULL IDENTITY(1,1) ,
[From] int NOT NULL ,
[To] int NOT NULL ,
[Subject] nvarchar(200) NOT NULL ,
[Message] ntext NOT NULL ,
[Created] datetime NOT NULL DEFAULT (getdate()) ,
[Folder] smallint NULL DEFAULT ((1)) ,
[IsRead] bit NOT NULL DEFAULT ((0)) ,
[ReadReceipt] bit NOT NULL DEFAULT ((0)) ,
[DeletedFromInbox] bit NOT NULL ,
[DeletedFromSendItems] bit NOT NULL 
)


GO

-- ----------------------------
-- Records of PrivateMessages
-- ----------------------------
BEGIN TRANSACTION
GO
SET IDENTITY_INSERT [PrivateMessages] ON
GO
SET IDENTITY_INSERT [PrivateMessages] OFF
GO
COMMIT TRANSACTION
GO

-- ----------------------------
-- Table structure for [Ratings]
-- ----------------------------
DROP TABLE [Ratings]
GO
CREATE TABLE [Ratings] (
[Id] int NOT NULL IDENTITY(1,1) ,
[TopicId] int NOT NULL ,
[UserId] int NOT NULL ,
[Star] int NOT NULL 
)


GO

-- ----------------------------
-- Records of Ratings
-- ----------------------------
BEGIN TRANSACTION
GO
SET IDENTITY_INSERT [Ratings] ON
GO
SET IDENTITY_INSERT [Ratings] OFF
GO
COMMIT TRANSACTION
GO

-- ----------------------------
-- Table structure for [Reports]
-- ----------------------------
DROP TABLE [Reports]
GO
CREATE TABLE [Reports] (
[Id] int NOT NULL IDENTITY(1,1) ,
[TopicId] int NOT NULL ,
[UserId] int NOT NULL ,
[IP] varchar(20) NOT NULL ,
[Reason] nvarchar(200) NULL ,
[Created] datetime NOT NULL 
)


GO
DBCC CHECKIDENT(N'[Reports]', RESEED, 3)
GO

-- ----------------------------
-- Records of Reports
-- ----------------------------
BEGIN TRANSACTION
GO
SET IDENTITY_INSERT [Reports] ON
GO
SET IDENTITY_INSERT [Reports] OFF
GO
COMMIT TRANSACTION
GO

-- ----------------------------
-- Table structure for [Requests]
-- ----------------------------
DROP TABLE [Requests]
GO
CREATE TABLE [Requests] (
[Id] int NOT NULL IDENTITY(1,1) ,
[Email] varchar(50) NULL ,
[ActiveCode] varchar(50) NOT NULL ,
[Created] datetime NOT NULL DEFAULT (getutcdate()) ,
[ActionType] int NOT NULL 
)


GO
DBCC CHECKIDENT(N'[Requests]', RESEED, 3)
GO

-- ----------------------------
-- Records of Requests
-- ----------------------------
BEGIN TRANSACTION
GO
SET IDENTITY_INSERT [Requests] ON
GO
INSERT INTO [Requests] ([Id], [Email], [ActiveCode], [Created], [ActionType]) VALUES (N'1', N'nasavietnam@gmail.com', N'a0e819d4-7374-47da-8d2b-9fabcf344256', N'2011-08-03 11:16:32.820', N'0');
INSERT INTO [Requests] ([Id], [Email], [ActiveCode], [Created], [ActionType]) VALUES (N'2', N'nasavietnam@gmail.com', N'a9fa4a17-c006-477e-992a-b6ce57d19fc3', N'2011-09-01 05:07:52.660', N'1');
INSERT INTO [Requests] ([Id], [Email], [ActiveCode], [Created], [ActionType]) VALUES (N'3', N'nasavietnam@gmail.com', N'd45b1823-7a34-404d-af5c-ae3fe030a9a5', N'2011-11-02 06:25:44.823', N'1');
GO
SET IDENTITY_INSERT [Requests] OFF
GO
COMMIT TRANSACTION
GO

-- ----------------------------
-- Table structure for [Roles]
-- ----------------------------
DROP TABLE [Roles]
GO
CREATE TABLE [Roles] (
[RoleId] int NOT NULL IDENTITY(1,1) ,
[RoleName] nvarchar(100) NOT NULL ,
[IsAutoStart] bit NOT NULL ,
[IsAdmin] bit NOT NULL DEFAULT ((0)) ,
[IsSuperModerator] bit NOT NULL ,
[CssClass] varchar(20) NULL ,
[AccessMask] varchar(4000) NULL ,
[AllowLogin] bit NOT NULL 
)


GO
DBCC CHECKIDENT(N'[Roles]', RESEED, 5)
GO

-- ----------------------------
-- Records of Roles
-- ----------------------------
BEGIN TRANSACTION
GO
SET IDENTITY_INSERT [Roles] ON
GO
INSERT INTO [Roles] ([RoleId], [RoleName], [IsAutoStart], [IsAdmin], [IsSuperModerator], [CssClass], [AccessMask], [AllowLogin]) VALUES (N'1', N'Admin', N'0', N'1', N'0', N'administrator', N'PostNewTopics:true|PostReplyToTopics:true|PostNewPolls:true|PostReplyToPolls:true|PostWithinLockedForums:false|PostWithinLockedTopics:false|EditOwnTopics:true|EditOwnPosts:true|EditLockedTopics:false|UpdateLastEditedBy:true|DeleteOwnTopics:true|DeleteOwnPosts:true|DeleteLockedPosts:false|AlwaysPostAprroved:false|QueueEdited:false|SendPostApprovedEmail:true|AllowPostReports:true|AllowSearch:true|MaxSearchResults:500|SearchResultsPerPage:25|MaxUploadStorage:10|AllowFileTypes:*|AllowDownloadAttach:true', N'1');
INSERT INTO [Roles] ([RoleId], [RoleName], [IsAutoStart], [IsAdmin], [IsSuperModerator], [CssClass], [AccessMask], [AllowLogin]) VALUES (N'2', N'Moderator', N'0', N'0', N'0', N'moderator', N'PostNewTopics:true|PostReplyToTopics:true|PostNewPolls:true|PostReplyToPolls:true|PostWithinLockedForums:false|PostWithinLockedTopics:false|EditOwnTopics:true|EditOwnPosts:true|EditLockedTopics:false|UpdateLastEditedBy:true|DeleteOwnTopics:true|DeleteOwnPosts:true|DeleteLockedPosts:false|AlwaysPostAprroved:false|QueueEdited:false|SendPostApprovedEmail:true|AllowPostReports:true|AllowSearch:true|MaxSearchResults:500|SearchResultsPerPage:25|MaxUploadStorage:10|AllowFileTypes:*|AllowDownloadAttach:true', N'1');
INSERT INTO [Roles] ([RoleId], [RoleName], [IsAutoStart], [IsAdmin], [IsSuperModerator], [CssClass], [AccessMask], [AllowLogin]) VALUES (N'3', N'V.I.P', N'0', N'0', N'0', N'vip', N'PostNewTopics:true|PostReplyToTopics:true|PostNewPolls:true|PostReplyToPolls:true|PostWithinLockedForums:false|PostWithinLockedTopics:false|EditOwnTopics:true|EditOwnPosts:true|EditLockedTopics:false|UpdateLastEditedBy:true|DeleteOwnTopics:true|DeleteOwnPosts:true|DeleteLockedPosts:false|AlwaysPostAprroved:false|QueueEdited:false|SendPostApprovedEmail:true|AllowPostReports:true|AllowSearch:true|MaxSearchResults:500|SearchResultsPerPage:25|MaxUploadStorage:10|AllowFileTypes:*|AllowDownloadAttach:true', N'1');
INSERT INTO [Roles] ([RoleId], [RoleName], [IsAutoStart], [IsAdmin], [IsSuperModerator], [CssClass], [AccessMask], [AllowLogin]) VALUES (N'4', N'Member', N'1', N'0', N'0', N'member', N'PostNewTopics:true|PostReplyToTopics:true|PostNewPolls:false|PostReplyToPolls:false|PostWithinLockedForums:false|PostWithinLockedTopics:false|AllowStickyPosts:false|EditOwnTopics:true|EditOwnPosts:true|EditLockedTopics:false|UpdateLastEditedBy:true|DeleteOwnTopics:true|DeleteOwnPosts:true|DeleteLockedPosts:false|AlwaysPostAprroved:false|QueueEdited:true|SendPostApprovedEmail:true|AllowPostReports:true|AllowReadForums:true|AllowReadTopics:true|SendTopicsViaEmail:false|AllowSendEmail:false|MaxEmailRecipients:5|SubscribeToForums:false|SubscribeToTopics:false|AllowSearch:true|MaxSearchResults:500|SearchResultsPerPage:50|MaxUploadStorage:0|AllowFileTypes:*|AllowDownloadAttach:true', N'1');
GO
SET IDENTITY_INSERT [Roles] OFF
GO
COMMIT TRANSACTION
GO

-- ----------------------------
-- Table structure for [Smiles]
-- ----------------------------
DROP TABLE [Smiles]
GO
CREATE TABLE [Smiles] (
[Id] int NOT NULL IDENTITY(1,1) ,
[Icon] varchar(20) NULL 
)


GO
DBCC CHECKIDENT(N'[Smiles]', RESEED, 9)
GO

-- ----------------------------
-- Records of Smiles
-- ----------------------------
BEGIN TRANSACTION
GO
SET IDENTITY_INSERT [Smiles] ON
GO
INSERT INTO [Smiles] ([Id], [Icon]) VALUES (N'1', N'1.gif');
INSERT INTO [Smiles] ([Id], [Icon]) VALUES (N'2', N'2.gif');
INSERT INTO [Smiles] ([Id], [Icon]) VALUES (N'3', N'8.gif');
INSERT INTO [Smiles] ([Id], [Icon]) VALUES (N'4', N'9.gif');
INSERT INTO [Smiles] ([Id], [Icon]) VALUES (N'5', N'19.gif');
INSERT INTO [Smiles] ([Id], [Icon]) VALUES (N'6', N'39.gif');
INSERT INTO [Smiles] ([Id], [Icon]) VALUES (N'7', N'42.gif');
INSERT INTO [Smiles] ([Id], [Icon]) VALUES (N'8', N'69.gif');
INSERT INTO [Smiles] ([Id], [Icon]) VALUES (N'9', N'77.gif');
GO
SET IDENTITY_INSERT [Smiles] OFF
GO
COMMIT TRANSACTION
GO

-- ----------------------------
-- Table structure for [Statisticals]
-- ----------------------------
DROP TABLE [Statisticals]
GO
CREATE TABLE [Statisticals] (
[ID] tinyint NOT NULL IDENTITY(1,1) ,
[Created] datetime NOT NULL ,
[TotalUsersOnline] int NULL ,
[Guests] int NULL ,
[TotalUsers] int NULL ,
[TotalTopics] int NULL ,
[TotalPosts] int NULL ,
[NewPostsInPast24Hours] int NULL ,
[NewTopicsInPast24Hours] int NULL ,
[NewUsersInPast24Hours] int NULL ,
[NewestUserID] nvarchar(104) NULL 
)


GO
DBCC CHECKIDENT(N'[Statisticals]', RESEED, 2)
GO

-- ----------------------------
-- Records of Statisticals
-- ----------------------------
BEGIN TRANSACTION
GO
SET IDENTITY_INSERT [Statisticals] ON
GO
INSERT INTO [Statisticals] ([ID], [Created], [TotalUsersOnline], [Guests], [TotalUsers], [TotalTopics], [TotalPosts], [NewPostsInPast24Hours], [NewTopicsInPast24Hours], [NewUsersInPast24Hours], [NewestUserID]) VALUES (N'1', N'2011-11-08 02:54:25.123', N'5', N'1', N'1', N'0', N'0', N'0', N'0', N'0', N'admin');
INSERT INTO [Statisticals] ([ID], [Created], [TotalUsersOnline], [Guests], [TotalUsers], [TotalTopics], [TotalPosts], [NewPostsInPast24Hours], [NewTopicsInPast24Hours], [NewUsersInPast24Hours], [NewestUserID]) VALUES (N'2', N'2011-11-10 08:34:07.270', N'1', N'0', N'1', N'0', N'0', N'0', N'0', N'0', N'admin');
GO
SET IDENTITY_INSERT [Statisticals] OFF
GO
COMMIT TRANSACTION
GO

-- ----------------------------
-- Table structure for [Subscriptions]
-- ----------------------------
DROP TABLE [Subscriptions]
GO
CREATE TABLE [Subscriptions] (
[Id] int NOT NULL IDENTITY(1,1) ,
[ForumId] int NULL ,
[TopicId] int NULL ,
[UserId] int NOT NULL ,
[NotificationType] smallint NOT NULL DEFAULT ((0)) ,
[Created] datetime NOT NULL ,
[LastEmail] datetime NULL 
)


GO
DBCC CHECKIDENT(N'[Subscriptions]', RESEED, 15)
GO

-- ----------------------------
-- Records of Subscriptions
-- ----------------------------
BEGIN TRANSACTION
GO
SET IDENTITY_INSERT [Subscriptions] ON
GO
SET IDENTITY_INSERT [Subscriptions] OFF
GO
COMMIT TRANSACTION
GO

-- ----------------------------
-- Table structure for [Tags]
-- ----------------------------
DROP TABLE [Tags]
GO
CREATE TABLE [Tags] (
[Id] int NOT NULL IDENTITY(1,1) ,
[T] nvarchar(50) NOT NULL ,
[Time] int NOT NULL DEFAULT ((0)) 
)


GO

-- ----------------------------
-- Records of Tags
-- ----------------------------
BEGIN TRANSACTION
GO
SET IDENTITY_INSERT [Tags] ON
GO
SET IDENTITY_INSERT [Tags] OFF
GO
COMMIT TRANSACTION
GO

-- ----------------------------
-- Table structure for [Topics]
-- ----------------------------
DROP TABLE [Topics]
GO
CREATE TABLE [Topics] (
[TopicId] int NOT NULL IDENTITY(1,1) ,
[UserId] int NOT NULL ,
[ForumId] int NOT NULL ,
[XForumId] int NULL ,
[ReplyTo] int NULL ,
[Priority] smallint NOT NULL DEFAULT ((0)) ,
[PriorityExpires] datetime NULL ,
[RedirectExpires] datetime NULL ,
[LastEdited] datetime NOT NULL DEFAULT (getutcdate()) ,
[Created] datetime NOT NULL DEFAULT (getutcdate()) ,
[IsApproved] bit NOT NULL DEFAULT ((0)) ,
[IsDeleted] bit NOT NULL DEFAULT ((0)) ,
[IsLocked] bit NOT NULL DEFAULT ((0)) ,
[PollId] int NULL ,
[TopicName] nvarchar(256) NULL ,
[RemoteUrl] nvarchar(256) NULL ,
[Tags] nvarchar(136) NULL ,
[EditedBy] nvarchar(100) NULL ,
[Reason] nvarchar(150) NULL ,
[Icon] varchar(20) NULL ,
[IP] varchar(20) NULL ,
[UserName] varchar(64) NULL ,
[Message] ntext NOT NULL ,
[Views] int NOT NULL DEFAULT ((1)) 
)


GO
DBCC CHECKIDENT(N'[Topics]', RESEED, 0)
GO

-- ----------------------------
-- Records of Topics
-- ----------------------------
BEGIN TRANSACTION
GO
SET IDENTITY_INSERT [Topics] ON
GO
SET IDENTITY_INSERT [Topics] OFF
GO
COMMIT TRANSACTION
GO

-- ----------------------------
-- Table structure for [Users]
-- ----------------------------
DROP TABLE [Users]
GO
CREATE TABLE [Users] (
[UserId] int NOT NULL IDENTITY(1,1) ,
[RoleId] int NULL ,
[UserName] nvarchar(50) NOT NULL ,
[Pwd] varchar(136) NOT NULL ,
[Email] varchar(63) NOT NULL ,
[FullName] nvarchar(63) NULL ,
[Signature] nvarchar(MAX) NULL ,
[Avatar] varchar(80) NULL ,
[RegDate] datetime NOT NULL DEFAULT (getutcdate()) ,
[LastLogin] datetime NOT NULL DEFAULT (getutcdate()) ,
[LastNotifyCheck] datetime NULL ,
[TotalVisits] int NOT NULL DEFAULT ((0)) ,
[TotalPosts] int NOT NULL DEFAULT ((0)) ,
[ThanksReceived] int NULL DEFAULT ((0)) ,
[ThanksSent] int NULL ,
[IsActived] bit NOT NULL DEFAULT ((0)) ,
[IsBanned] bit NOT NULL DEFAULT ((0)) ,
[Sex] bit NULL ,
[Birthday] datetime NULL ,
[Address] nvarchar(136) NULL ,
[YahooId] nvarchar(36) NULL ,
[SkypeId] nvarchar(36) NULL ,
[MSN] nvarchar(36) NULL ,
[AOL] nvarchar(36) NULL ,
[Website] nvarchar(99) NULL ,
[Blog] nvarchar(99) NULL ,
[Gallery] nvarchar(99) NULL ,
[Hobby] nvarchar(136) NULL ,
[Bio] nvarchar(136) NULL ,
[Note] nvarchar(MAX) NULL ,
[TimeZone] float(53) NOT NULL ,
[SkinPath] nvarchar(36) NULL 
)


GO
DBCC CHECKIDENT(N'[Users]', RESEED, 0)
GO

-- ----------------------------
-- Records of Users
-- ----------------------------
BEGIN TRANSACTION
GO
SET IDENTITY_INSERT [Users] ON
GO
INSERT INTO [Users] ([UserId], [RoleId], [UserName], [Pwd], [Email], [FullName], [Signature], [Avatar], [RegDate], [LastLogin], [LastNotifyCheck], [TotalVisits], [TotalPosts], [ThanksReceived], [ThanksSent], [IsActived], [IsBanned], [Sex], [Birthday], [Address], [YahooId], [SkypeId], [MSN], [AOL], [Website], [Blog], [Gallery], [Hobby], [Bio], [Note], [TimeZone], [SkinPath]) VALUES (N'764', N'1', N'admin', N'998ed4d621742d0c2d85ed84173db569afa194d4597686cae947324aa58ab4bb', N'nasavietnam@gmail.com', null, N'Admin', null, N'2011-11-04 03:56:34.413', N'2011-11-08 02:54:53.937', null, N'8', N'0', N'0', null, N'0', N'0', null, null, null, null, null, null, null, null, null, null, null, null, null, N'7', null);
GO
SET IDENTITY_INSERT [Users] OFF
GO
COMMIT TRANSACTION
GO

-- ----------------------------
-- Table structure for [Votes]
-- ----------------------------
DROP TABLE [Votes]
GO
CREATE TABLE [Votes] (
[Id] int NOT NULL IDENTITY(1,1) ,
[AnswerId] int NOT NULL ,
[UserId] int NOT NULL 
)


GO

-- ----------------------------
-- Records of Votes
-- ----------------------------
BEGIN TRANSACTION
GO
SET IDENTITY_INSERT [Votes] ON
GO
SET IDENTITY_INSERT [Votes] OFF
GO
COMMIT TRANSACTION
GO

-- ----------------------------
-- Procedure structure for [Activity_List]
-- ----------------------------
DROP PROCEDURE [Activity_List]
GO

CREATE PROC [Activity_List]
				@PageIndex INT=1,
				@PageSize INT = 25,
				@TotalRows INT=0 OUT
AS

BEGIN
	SET NOCOUNT ON
	
	SELECT @TotalRows=COUNT(1) FROM Activities  WHERE IsPrivated=0;
	
	WITH CTE	
		 AS
			(
			SELECT 
				ROW_NUMBER() OVER (ORDER BY LastAction DESC) AS RowNum,
				UserId,				
				UserName,
				Location,
				PageTitle,
				IP,
				[Login],
				LastAction	,
				Browser,
				[Platform]					
					
			FROM  Activities  WHERE IsPrivated=0
			)
			
			SELECT * FROM CTE WHERE RowNum BETWEEN (@PageIndex - 1) * @PageSize + 1 AND @PageIndex * @PageSize;
	
	
	
END

GO

-- ----------------------------
-- Procedure structure for [Activity_Save]
-- ----------------------------
DROP PROCEDURE [Activity_Save]
GO

CREATE PROC [Activity_Save]
				@SessionId VARCHAR(36),
				@UserId INT = NULL,
				@UserName NVARCHAR(50)=NULL,
				@IP VARCHAR(20),				
				@PageTitle NVARCHAR(250)=NULL,
				@Location NVARCHAR(250),				
				@ForumId INT =NULL,
				@TopicId INT=NULL,
				@Browser NVARCHAR(50)=NULL,
				@Platform NVARCHAR(50)=NULL,
				@IsPrivated BIT,
				@PastMinutes INT=60
AS
	BEGIN	
		
		SET NOCOUNT ON;
				
			IF EXISTS (SELECT SessionID FROM   Activities WITH(NOLOCK) WHERE  SessionID = @SessionID)	
               BEGIN
               		UPDATE Activities SET
						   LastAction = GETUTCDATE(),
						   PageTitle=@PageTitle,
						   Location = @Location,
						   ForumID = @ForumID,
						   TopicID = @TopicID,
						   UserName=@UserName,
						   @UserId=@UserId,
						   IsPrivated=@IsPrivated
					WHERE  SessionID = @SessionID
               END					
				
			ELSE
					BEGIN
						INSERT INTO Activities
								   (SessionID,                           
									UserID,
									UserName,
									IP,
									[Login],									
									LastAction,
									PageTitle,
									Location,
									ForumID,
									TopicID,
									Browser,
									[Platform],
									IsPrivated
								   )
						VALUES     (@SessionID,                           
									@UserID,
									@UserName,
									@IP,
									GETUTCDATE(),
									GETUTCDATE(),
									@PageTitle,
									@Location,
									@ForumID,
									@TopicID,
									@Browser,
									@Platform,
									@IsPrivated);  
					EXEC Statistical_UpdateMaxUsersOnline @PastMinutes
					END
			 
			 
			 -- remove duplicate users		
		    DELETE FROM Activities
		    WHERE  (UserId=@UserId AND UserId<>0 AND SessionID <> @SessionID) OR (DATEADD(Mi, @PastMinutes, LastAction)< GETUTCDATE())
		
	END
	
	

GO

-- ----------------------------
-- Procedure structure for [EmailQueue_Delete]
-- ----------------------------
DROP PROCEDURE [EmailQueue_Delete]
GO

CREATE PROC [EmailQueue_Delete]
	@Id INT
AS
BEGIN
	DELETE FROM EmailQueue WHERE Id =@Id
END

GO

-- ----------------------------
-- Procedure structure for [EmailQueue_List]
-- ----------------------------
DROP PROCEDURE [EmailQueue_List]
GO

CREATE PROC [EmailQueue_List]
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT TOP(5)		
		Id,
		[From],
		[To],
		[Subject],
		Body,
		Created,
		Sended
	FROM
		EmailQueue 
	ORDER BY Priority DESC,Id
END

GO

-- ----------------------------
-- Procedure structure for [EmailQueue_Save]
-- ----------------------------
DROP PROCEDURE [EmailQueue_Save]
GO

--[EmailQueue_Save] 'admin@mvcforum.net','nasavietnam@gmail.com','MvcForum.Net - Subject','Body',0

CREATE PROC [EmailQueue_Save]
	@From VARCHAR(64),
	@To VARCHAR(64),
	@Subject NVARCHAR(100),	
	@Body NTEXT,
	@Priority SMALLINT
AS
BEGIN
	INSERT INTO EmailQueue ([From],[To],[Subject],[Body],Created,Priority) VALUES(@From,@To,@Subject,@Body,GETUTCDATE(),@Priority);
	
	
END
	

GO

-- ----------------------------
-- Procedure structure for [Forum_CalcLastPost]
-- ----------------------------
DROP PROCEDURE [Forum_CalcLastPost]
GO

-- [Forum_CalcLastPost] 2
CREATE PROC [Forum_CalcLastPost]
	@ForumId INT,
	@Topics INT,
	@Replies INT
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @ForumTemp TABLE 
	( 
		ForumId INT,
		ParentId INT 	     
	);
	DECLARE @TotalTopics BIGINT,  
	@TotalReplies BIGINT,
	@LastPostId INT,	
	@Subject NVARCHAR(250),
	@UserName NVARCHAR(64),
	@LastPostDate DATETIME;
	
	-- Parent to childs	
	WITH PTC AS
	(
	   -- Base case
	   SELECT
		  ForumId,
		  ParentId		    
		 
	   FROM Forums F WITH(NOLOCK)
	   WHERE ParentId =@ForumId	OR ForumId=@ForumId   
	   UNION ALL
	   -- Recursive step
	   SELECT
		  F.ForumId,
		  F.ParentId		 
		
	   FROM Forums F WITH(NOLOCK)
		  INNER JOIN PTC C ON C.ForumId = F.ParentId
	)
	
	-- Insert To Table temp 4 reuse
	INSERT INTO @ForumTemp
	SELECT * FROM PTC OPTION(MAXRECURSION 0);
	
	--SELECT  @TotalTopics=(SELECT COUNT (1) FROM Topics T WITH(NOLOCK) INNER JOIN @ForumTemp C ON T.ForumId=C.ForumId WHERE IsApproved=1 AND ReplyTo IS NULL);
	--SELECT  @TotalReplies=(SELECT COUNT (1) FROM Topics T WITH(NOLOCK) INNER JOIN @ForumTemp C ON T.ForumId=C.ForumId WHERE IsApproved=1 AND ReplyTo>0);
	
	SELECT	TOP(1)		
		 	@LastPostId=T.TopicId,
			@UserName=T.UserName,			
			@LastPostDate= T.Created,
			@Subject=ISNULL(T.TopicName, '[RE:]'+ T2.TopicName)
	FROM Topics T WITH(NOLOCK) INNER JOIN @ForumTemp F ON T.ForumId=F.ForumId
	LEFT JOIN Topics T2 ON T.ReplyTo=T2.TopicId 
	WHERE T.IsApproved=1
	ORDER BY  T.TopicId DESC	


	;WITH CTP AS 
	(
			
		SELECT
      ForumId,
      ParentId
     
   FROM Forums WITH(NOLOCK)
   WHERE ForumId =@ForumId
   UNION ALL

   -- Recursive step
   SELECT
      F.ForumId,
      F.ParentId     
   FROM Forums F WITH(NOLOCK)
      INNER JOIN CTP C ON
         F.ForumId = C.ParentId
	)

	UPDATE Forums
	SET		
		TotalTopics=TotalTopics+@Topics,
		TotalReplies=TotalReplies+@Replies,
		LastPostDate=@LastPostDate,
		LastPostId=@LastPostId,
		LastPostTitle=@Subject,
		LastUserPost=@UserName	
	WHERE ForumId IN (SELECT ForumId FROM CTP) 
	OPTION(MAXRECURSION 0)
		
	
END


GO

-- ----------------------------
-- Procedure structure for [Forum_Delete]
-- ----------------------------
DROP PROCEDURE [Forum_Delete]
GO

CREATE PROC [Forum_Delete]
		@Ids VARCHAR(max)
AS
BEGIN
	SET NOCOUNT ON
	
	DELETE FROM Forums WHERE ForumId IN (SELECT ID FROM SplitStringToTable(@Ids))
	
END

 

GO

-- ----------------------------
-- Procedure structure for [Forum_Detail]
-- ----------------------------
DROP PROCEDURE [Forum_Detail]
GO
CREATE PROC [Forum_Detail]
			@ForumId INT
AS
BEGIN
	SET NOCOUNT ON;
	SELECT * FROM Forums WHERE ForumId=@ForumId;
END

GO

-- ----------------------------
-- Procedure structure for [Forum_GetLastPost]
-- ----------------------------
DROP PROCEDURE [Forum_GetLastPost]
GO

-- [Forum_GetLastPost] 2
CREATE PROC [Forum_GetLastPost]
	@ForumId INT,
	@TotalTopics INT =0 OUT,
	@TotalReplies INT =0 OUT
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @ForumTemp TABLE 
	( 
		ForumId INT,
		ParentId INT 	     
	);
	
	-- Parent to childs	
	WITH CTE (ForumId,ParentId) AS
	(
	   -- Base case
	   SELECT
		  ForumId,
		  ParentId		     
		 
	   FROM Forums F WITH(NOLOCK)
	   WHERE ParentId =@ForumId	OR ForumId=@ForumId   
	   UNION ALL
	   -- Recursive step
	   SELECT
		  F.ForumId,
		  F.ParentId		 
		
	   FROM Forums F WITH(NOLOCK)
		  INNER JOIN CTE C ON C.ForumId = F.ParentId
	)
	-- Insert To Table temp 4 reuse
	INSERT INTO @ForumTemp
	SELECT * FROM CTE OPTION(MAXRECURSION 0);
	
	SELECT  @TotalTopics=(SELECT COUNT (1) FROM Topics T WITH(NOLOCK) INNER JOIN @ForumTemp C ON T.ForumId=C.ForumId WHERE IsApproved=1 AND ReplyTo IS NULL);
	SELECT  @TotalReplies=(SELECT COUNT (1) FROM Topics T WITH(NOLOCK) INNER JOIN @ForumTemp C ON T.ForumId=C.ForumId WHERE IsApproved=1 AND ReplyTo>0);
	
	SELECT 	TOP(1)		
			T.TopicId,
			T.UserName,
			T.UserId,
			T.Created,
			ISNULL(T.TopicName, '[RE:]'+ T2.TopicName) AS TopicName
	FROM Topics T WITH(NOLOCK) INNER JOIN @ForumTemp F ON T.ForumId=F.ForumId
	LEFT JOIN Topics T2 ON T.ReplyTo=T2.TopicId 
	WHERE T.IsApproved=1
	ORDER BY  TopicId DESC			
	
END


GO

-- ----------------------------
-- Procedure structure for [Forum_List]
-- ----------------------------
DROP PROCEDURE [Forum_List]
GO

--[Forum_List] 0

CREATE PROC [Forum_List]
		@ParentId INT
			
		
AS
BEGIN
	SET NOCOUNT ON;
		
	WITH 
		CTE AS(
		SELECT 
			ForumId,
			ParentId,
			IsLocked,
			ForumName,
			VisibleAtHomePage,
			AllowPostWithIn,
			TotalTopics,
			TotalReplies,
			LastPostId,	
			LastPostTitle,
			LastPostDate,	
			LastUserPost,
			Keywords,	
			[Description],
			AllowRoles,
			SortDirection,
			OrderBy,
			IncreaseMemberPosts,
			TopicsPerPage,
			PostsPerPage,
			AllowSearch,
			1 as [Level],
			CAST(RIGHT('000'+CAST(SortOrder AS VARCHAR(50)),2) AS VARCHAR) AS LevelName			
			FROM Forums F WITH(NOLOCK)
			WHERE ParentId=@ParentId
			
		UNION ALL
		
		SELECT 
			F.ForumId,
			F.ParentId	,
			F.IsLocked,
			F.ForumName,
			F.VisibleAtHomePage,
			F.AllowPostWithIn,
			F.TotalTopics,
			F.TotalReplies,
			F.LastPostId,
			F.LastPostTitle,
			F.LastPostDate,
			F.LastUserPost,
			F.Keywords,
			F.[Description],	
			F.AllowRoles,	
			F.SortDirection,
			F.OrderBy,
			F.IncreaseMemberPosts,		
			F.TopicsPerPage,
			F.PostsPerPage,
			F.AllowSearch,	
			H.[Level]+1 as [Level],
			CAST(H.LevelName+ '-' + RIGHT('000'+CAST(F.SortOrder AS VARCHAR(50)),2) AS VARCHAR) as LevelName			
			FROM Forums F WITH(NOLOCK) 
			INNER JOIN CTE H ON H.ForumId=F.ParentId		
			
	)
	
	SELECT * FROM CTE ORDER BY LevelName
	OPTION (MAXRECURSION 0); 
		
		
	
END

GO

-- ----------------------------
-- Procedure structure for [Forum_ListAll]
-- ----------------------------
DROP PROCEDURE [Forum_ListAll]
GO

--[Forum_List] 0

create PROC [Forum_ListAll]
		@ParentId INT
			
		
AS
BEGIN
	SET NOCOUNT ON;
		
	WITH 
		CTE AS(
		SELECT 
			ForumId,
			ParentId,
			IsLocked,
			ForumName,
			VisibleAtHomePage,
			AllowPostWithIn,
			TotalTopics,
			TotalReplies,
			LastPostId,	
			LastPostTitle,
			LastPostDate,	
			LastUserPost,
			Keywords,	
			[Description],
			AllowRoles,
			SortDirection,
			OrderBy,
			IncreaseMemberPosts,
			TopicsPerPage,
			PostsPerPage,
			AllowSearch,
			1 as [Level],
			CAST(RIGHT('000'+CAST(SortOrder AS VARCHAR(50)),2) AS VARCHAR) AS LevelName			
			FROM Forums F WITH(NOLOCK)
			WHERE ParentId=@ParentId
			
		UNION ALL
		
		SELECT 
			F.ForumId,
			F.ParentId	,
			F.IsLocked,
			F.ForumName,
			F.VisibleAtHomePage,
			F.AllowPostWithIn,
			F.TotalTopics,
			F.TotalReplies,
			F.LastPostId,
			F.LastPostTitle,
			F.LastPostDate,
			F.LastUserPost,
			F.Keywords,
			F.[Description],	
			F.AllowRoles,	
			F.SortDirection,
			F.OrderBy,
			F.IncreaseMemberPosts,		
			F.TopicsPerPage,
			F.PostsPerPage,
			F.AllowSearch,	
			H.[Level]+1 as [Level],
			CAST(H.LevelName+ '-' + RIGHT('000'+CAST(F.SortOrder AS VARCHAR(50)),2) AS VARCHAR) as LevelName			
			FROM Forums F WITH(NOLOCK) 
			INNER JOIN CTE H ON H.ForumId=F.ParentId		
			
	)
	
	SELECT * FROM CTE ORDER BY LevelName
	OPTION (MAXRECURSION 0); 
		
		
	
END

GO

-- ----------------------------
-- Procedure structure for [Forum_ResetCounterAndLastPost]
-- ----------------------------
DROP PROCEDURE [Forum_ResetCounterAndLastPost]
GO
CREATE PROCEDURE [Forum_ResetCounterAndLastPost]
		
AS
BEGIN

	SET NOCOUNT ON;
	DECLARE @ForumId INT
	DECLARE @TotalTopics BIGINT,  
		@TotalReplies BIGINT=0,		
		@LastPostId INT=NULL,	
		@Subject NVARCHAR(250)=NULL,
		@UserName NVARCHAR(64)=NULL,
		@LastPostDate DATETIME=NULL
	DECLARE @ForumTemp TABLE (ForumId INT);
	DECLARE ROW_CURSOR CURSOR FOR SELECT ForumId FROM Forums;

	OPEN ROW_CURSOR 
	FETCH NEXT FROM ROW_CURSOR INTO @ForumId          	
	
	WHILE @@FETCH_STATUS = 0
	   BEGIN
		-- reset old data
		
		DELETE FROM @ForumTemp;

		SET @LastPostId=NULL;
		SET @Subject=NULL;
		SET @UserName=NULL;
		SET @LastPostDate=NULL;

		;WITH PTC AS
		(
			SELECT
			  ForumId,
			  ParentId	 
			   FROM Forums F WITH(NOLOCK)
			   WHERE ParentId = @ForumId  OR ForumId=@ForumId
					
			   UNION ALL
			   -- Recursive step
			   SELECT
				  F.ForumId,
				  F.ParentId		 
				
			   FROM Forums F WITH(NOLOCK)
				  INNER JOIN PTC C ON C.ForumId = F.ParentId 		
			
		)
		
		INSERT INTO @ForumTemp SELECT DISTINCT ForumId FROM PTC OPTION(MAXRECURSION 0);
		
		SELECT  @TotalTopics=(SELECT  COUNT (1) FROM Topics T  INNER JOIN @ForumTemp C ON T.ForumId=C.ForumId WHERE IsApproved=1 AND ReplyTo IS NULL),
			   @TotalReplies=(SELECT  COUNT (1) FROM Topics T INNER JOIN @ForumTemp C ON T.ForumId=C.ForumId WHERE IsApproved=1 AND ReplyTo>0)
		
		SELECT TOP(1)		
		 	@LastPostId=T.TopicId,
			@UserName=T.UserName,			
			@LastPostDate= T.Created,
			@Subject=ISNULL(T.TopicName, '[RE:]'+ T2.TopicName)
			FROM Topics T INNER JOIN @ForumTemp F ON T.ForumId=F.ForumId
			LEFT JOIN Topics T2 ON T.ReplyTo=T2.TopicId 
			WHERE T.IsApproved=1  
			ORDER BY  T.TopicId DESC;
		--Update Forums
		
		UPDATE Forums
		SET		
			TotalTopics=@TotalTopics,
			TotalReplies=@TotalReplies,
			LastPostDate=@LastPostDate,
			LastPostId=@LastPostId,
			LastPostTitle=@Subject,
			LastUserPost=@UserName	
		WHERE ForumId=@ForumId;
		
	   FETCH NEXT FROM ROW_CURSOR INTO @ForumId          

	   END

	CLOSE ROW_CURSOR

	DEALLOCATE ROW_CURSOR
	

END

GO

-- ----------------------------
-- Procedure structure for [Forum_Save]
-- ----------------------------
DROP PROCEDURE [Forum_Save]
GO

CREATE PROC [Forum_Save]
		@ForumId INT =0,
		@Icon VARCHAR(150),
		@ForumName NVARCHAR(250),
		@ParentId INT,
		@Keywords NVARCHAR(250),
		@Description NVARCHAR(440),
		@SortOrder SMALLINT,
		@RemoteUrl NVARCHAR(256),
		@IsLocked BIT,
		@AllowPostWithIn BIT,
		@ModerateType SMALLINT,
		@AllowRoles VARCHAR(50),
		@VisibleAtHomePage BIT,
		@IncreaseMemberPosts BIT,
		@OrderBy TINYINT,
		@SortDirection TINYINT,
		@TopicsPerPage INT,
		@PostsPerPage INT,
		@AllowSearch BIT
			
		
AS
BEGIN
	IF(@ForumId>0)
	BEGIN
		UPDATE Forums
		SET
			ForumName = @ForumName,
			Icon=@Icon,			
			ParentId = @ParentId,
			Keywords = @Keywords,
			[Description] = @Description,
			SortOrder = @SortOrder,	  
			RemoteUrl = @RemoteUrl,
			IsLocked = @IsLocked,
			AllowPostWithIn = @AllowPostWithIn,
			ModerateType = @ModerateType,
			AllowRoles = @AllowRoles,
			VisibleAtHomePage = @VisibleAtHomePage,
			IncreaseMemberPosts=@IncreaseMemberPosts,
			OrderBy=@OrderBy,
			SortDirection=@SortDirection,
			TopicsPerPage=@TopicsPerPage,
			PostsPerPage=@PostsPerPage,
			AllowSearch=@AllowSearch
			
		WHERE ForumId=@ForumId
			
	END
	ELSE 
	BEGIN
		 INSERT	 INTO Forums
		 (
		 	
		 	ForumName,
		 	Icon,
		 	ParentId,
		 	Keywords,
		 	[Description],
		 	SortOrder,
		 	RemoteUrl,
		 	IsLocked,
		 	AllowPostWithIn,
		 	ModerateType,
		 	AllowRoles,
		 	VisibleAtHomePage,
		 	IncreaseMemberPosts,
		 	OrderBy,
		 	SortDirection,
		 	TopicsPerPage,
		 	PostsPerPage,
		 	AllowSearch
		 )
		 VALUES
		 (
		 	@ForumName,
		 	@Icon,
		 	@ParentId	,
		 	@Keywords	,
		 	@Description	,
		 	@SortOrder	,
		 	@RemoteUrl	,
		 	@IsLocked	,
		 	@AllowPostWithIn	,
		 	@ModerateType	,
		 	@AllowRoles	,
		 	@VisibleAtHomePage,
		 	@IncreaseMemberPosts,
		 	@OrderBy,
		 	@SortDirection,
		 	@TopicsPerPage,
		 	@PostsPerPage,
		 	@AllowSearch
		 ) 
		 
	END
END


GO

-- ----------------------------
-- Procedure structure for [Forum_StatisUpdateMaxOnline]
-- ----------------------------
DROP PROCEDURE [Forum_StatisUpdateMaxOnline]
GO
CREATE PROCEDURE [Forum_StatisUpdateMaxOnline]
					@PastMinutes INT=15
AS
DECLARE @TotalUserOnline INT
BEGIN
	SET @TotalUserOnline = (SELECT COUNT(1) FROM UsersActions WHERE (DATEADD(Mi, @PastMinutes, LastAction)> GETUTCDATE()))
	UPDATE MF_Statistics 
	 SET 
			Created=GETUTCDATE(),
			TotalUsersOnline=@TotalUserOnline
	WHERE
		TotalUsersOnline<@TotalUserOnline
END

GO

-- ----------------------------
-- Procedure structure for [Forum_SubList]
-- ----------------------------
DROP PROCEDURE [Forum_SubList]
GO

CREATE PROC [Forum_SubList]
		@ParentId INT		
		
AS
BEGIN
	SET NOCOUNT ON;
		
	WITH 
		CTE AS
		(
			SELECT 
				ForumId,
				ParentId,			
				1 as [Level],
				CAST(RIGHT('000'+CAST(SortOrder AS VARCHAR(50)),2) AS VARCHAR) AS LevelName			
				FROM Forums F WITH(NOLOCK)
				WHERE ParentId=@ParentId
			
			UNION ALL
		
			SELECT 
				F.ForumId,
				F.ParentId,			
				H.[Level]+1 as [Level],
				CAST(H.LevelName+ '-' + RIGHT('000'+CAST(F.SortOrder AS VARCHAR(50)),2) AS VARCHAR) as LevelName			
				FROM Forums F WITH(NOLOCK) 
				INNER JOIN CTE H ON H.ForumId=F.ParentId					
		),
		CTE2 AS
		(
			Select 
				C.*,
				(SELECT COUNT(1) from Topics T where T.ForumId=C.ForumId) AS Posts
			From CTE C

		)
	
	SELECT * FROM CTE2 
	OPTION (MAXRECURSION 0); 
		
		
	
END


GO

-- ----------------------------
-- Procedure structure for [Forum_SubToParent]
-- ----------------------------
DROP PROCEDURE [Forum_SubToParent]
GO
CREATE  PROC [Forum_SubToParent] 
		@ForumId INT
AS
BEGIN
	
WITH CTE (ForumId,ParentId, ForumName,[Level]) AS
(
   -- Base case
   SELECT
      ForumId,
      ParentId,
      ForumName,      
      1 as [Level]
   FROM Forums WITH(NOLOCK)
   WHERE ForumId =@ForumId
   UNION ALL
   -- Recursive step
   SELECT
      F.ForumId,
      F.ParentId,
      F.ForumName,     
      C.[Level] + 1 AS [Level]
   FROM Forums F WITH(NOLOCK)
      INNER JOIN CTE C ON
         F.ForumId = C.ParentId
)
SELECT * FROM CTE ORDER BY [Level] DESC 
 
END


GO

-- ----------------------------
-- Procedure structure for [Forum_ToggleLock]
-- ----------------------------
DROP PROCEDURE [Forum_ToggleLock]
GO

CREATE PROC [Forum_ToggleLock]
	@Id INT
AS
BEGIN
	UPDATE Forums SET IsLocked= IsLocked^1 WHERE ForumId=@Id
END
	

GO

-- ----------------------------
-- Procedure structure for [Forum_TotalTopicsOrPosts]
-- ----------------------------
DROP PROCEDURE [Forum_TotalTopicsOrPosts]
GO
CREATE PROCEDURE [Forum_TotalTopicsOrPosts] 
			@ForumId INT,
			@TopicCount INT = 0 OUTPUT,
			@PostCount INT = 0 OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
	WITH CTE1 (ForumId,ParentId,HierarchyLevel) AS
	(
  
	   SELECT
		  ForumId,		 
		  ParentId,      
		  1 as HierarchyLevel
	   FROM Forums
	   WHERE ForumId =@ForumId OR ParentId=@ForumId
	   UNION ALL
	   -- Recursive step
	   SELECT
		  e.ForumId,     
		  e.ParentId,     
		  eh.HierarchyLevel + 1 AS HierarchyLevel
	   FROM Forums e
		  INNER JOIN CTE1 eh ON
			 e.ParentId = eh.ForumId
	)
		
	SELECT @TopicCount=COUNT(1) FROM Topics  WHERE IsApproved=1 AND IsDeleted=0 AND ReplyTo IS NULL AND ForumId IN (SELECT ForumId FROM CTE1);
	
	---------Count Posts-------------
	WITH CTE2 (ForumId,ParentId,HierarchyLevel) AS
	(
  
	   SELECT
		  ForumId,		 
		  ParentId,      
		  1 as HierarchyLevel
	   FROM Forums
	   WHERE ForumId =@ForumId OR ParentId=@ForumId
	   UNION ALL
	   -- Recursive step
	   SELECT
		  e.ForumId,     
		  e.ParentId,     
		  eh.HierarchyLevel + 1 AS HierarchyLevel
	   FROM Forums e
		  INNER JOIN CTE2 eh ON
			 e.ParentId = eh.ForumId
	)
	SELECT @PostCount=COUNT(1) FROM Topics  WHERE IsApproved=1 and IsDeleted=0 AND ReplyTo IS NOT NULL AND ForumId IN (SELECT ForumId FROM CTE2)	;
		 
END

GO

-- ----------------------------
-- Procedure structure for [Forum_UpdateLastPost]
-- ----------------------------
DROP PROCEDURE [Forum_UpdateLastPost]
GO

CREATE PROC [Forum_UpdateLastPost]
		@ForumId INT,	
		@TotalTopics INT,
		@TotalReplies INT,	
		@UserName VARCHAR(64),
		@LastPostId INT,
		@LastPostDate DATETIME,
		@LastPostTitle NVARCHAR(250)
AS
BEGIN
	SET NOCOUNT ON
		
	UPDATE Forums
	SET		
		TotalTopics=@TotalTopics,
		TotalReplies=@TotalReplies,
		LastPostDate=@LastPostDate,
		LastPostId=@LastPostId,
		LastPostTitle=@LastPostTitle,
		LastUserPost=@UserName	
	WHERE ForumId = @ForumId
END

GO

-- ----------------------------
-- Procedure structure for [IPLock_Delete]
-- ----------------------------
DROP PROCEDURE [IPLock_Delete]
GO

CREATE PROC [IPLock_Delete]
		@Ids VARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON
	
	DELETE FROM IPLocks WHERE Id IN (SELECT ID FROM SplitStringToTable(@Ids))
	
END

GO

-- ----------------------------
-- Procedure structure for [IPLock_Detail]
-- ----------------------------
DROP PROCEDURE [IPLock_Detail]
GO

CREATE PROC [IPLock_Detail]
		@Id INT
AS
BEGIN
	SELECT Id,Mask,Reason,Expires FROM IPLocks WHERE Id=@Id
END

GO

-- ----------------------------
-- Procedure structure for [IPLock_List]
-- ----------------------------
DROP PROCEDURE [IPLock_List]
GO
CREATE PROC [IPLock_List]
			
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT  Mask FROM IPLocks WHERE Expires>GETUTCDATE();
END


GO

-- ----------------------------
-- Procedure structure for [IPlock_ListAll]
-- ----------------------------
DROP PROCEDURE [IPlock_ListAll]
GO

CREATE PROC [IPlock_ListAll]
			
AS
BEGIN
	SET NOCOUNT ON
	
	SELECT Id,Mask,Reason,Expires FROM IPLocks ORDER BY Expires
END

GO

-- ----------------------------
-- Procedure structure for [IPLock_Save]
-- ----------------------------
DROP PROCEDURE [IPLock_Save]
GO

CREATE PROC [IPLock_Save]
		@Id INT,
		@Mask VARCHAR(20),
		@Reason NVARCHAR(250),
		@Expires DATETIME
AS
BEGIN
	IF @Id>0
	BEGIN
		UPDATE IPLocks SET Mask=@Mask,Reason=@Reason,Expires=@Expires WHERE Id=@Id
	END
	ELSE
		
		IF NOT EXISTS (SELECT 1 FROM IPLocks WHERE Mask=@Mask)		
		BEGIN
			INSERT INTO IPLocks
			(
				
				Mask,
				Reason,
				Expires
			)
			VALUES
			(
				@Mask,
				@Reason,
				@Expires
			)
		END
		ELSE
			UPDATE IPLocks SET	Reason = @Reason , Expires=@Expires WHERE Mask=@Mask
END

GO

-- ----------------------------
-- Procedure structure for [Like_List]
-- ----------------------------
DROP PROCEDURE [Like_List]
GO

CREATE PROC [Like_List]
		@TopicId INT,
		@PageIndex INT,
		@PageSize INT
AS
BEGIN
	SET NOCOUNT ON;
	
	WITH CTE AS (
		SELECT 
		ROW_NUMBER() OVER (ORDER BY TopicId) AS RowNum,
		TopicId						
		FROM Topics WITH(NOLOCK)
		WHERE TopicId=@TopicId OR ReplyTo=@TopicId 
	)
	
	SELECT 
		U.UserId,		
		U.UserName,
		L.TopicId,
		L.Created
	FROM CTE T INNER JOIN Likes L WITH(NOLOCK) ON T.TopicId=L.TopicId 
				INNER JOIN Users U WITH(NOLOCK) ON L.FromId=U.UserId
	WHERE T.RowNum BETWEEN (@PageIndex - 1) * @PageSize + 1 AND @PageIndex * @PageSize
	ORDER BY L.Created DESC	
END


GO

-- ----------------------------
-- Procedure structure for [Like_Save]
-- ----------------------------
DROP PROCEDURE [Like_Save]
GO
														  
CREATE PROC [Like_Save]
			@TopicId INT,
			@FromId INT,
			@ToId INT,
			@Action INT =1 -- 1: Add , 0:Remove
AS
BEGIN
	SET NOCOUNT ON;
	
	IF(@Action=1 AND NOT EXISTS (SELECT 1 FROM Likes WHERE FromId=@FromId AND TopicId=@TopicId))  
	BEGIN
		INSERT INTO Likes
		(
			TopicId,
			FromId,			
			ToId,
			Created
		)
		VALUES
		(
			@TopicId,
			@FromId,
			@ToId,			
			GETUTCDATE()	
			
			
		)
	END
	ELSE
		BEGIN
			DELETE FROM Likes WHERE TopicId=@TopicId AND FromId=@FromId;
		END
END

GO

-- ----------------------------
-- Procedure structure for [LogIn]
-- ----------------------------
DROP PROCEDURE [LogIn]
GO
CREATE PROC [LogIn]
		@UserName NVARCHAR(65),
		@Pwd VARCHAR(136),
		@IsUpdateLogin BIT=0
AS
BEGIN
	SET NOCOUNT ON;
	
	IF(@IsUpdateLogin=1)
		BEGIN
			UPDATE Users  
			SET LastLogin=GETUTCDATE(),TotalVisits=TotalVisits+1
			WHERE UserName=@UserName
		END
	
	SELECT UserId,UserName,IsActived,Email,Avatar,Sex,TimeZone,SkinPath,U.LastNotifyCheck,U.RoleId,R.IsSuperModerator, R.IsAdmin,R.AccessMask
	FROM Users U INNER JOIN Roles R
	ON R.RoleId = U.RoleId 	WHERE U.IsBanned=0 AND (UserName=@UserName OR Email=@UserName) AND Pwd=@Pwd AND R.AllowLogin=1;
	
	
END


GO

-- ----------------------------
-- Procedure structure for [Moderator_Delete]
-- ----------------------------
DROP PROCEDURE [Moderator_Delete]
GO

CREATE PROC [Moderator_Delete]
		@Ids VARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON
	
	DELETE FROM Moderators WHERE Id IN (SELECT ID FROM SplitStringToTable(@Ids))
	
END


GO

-- ----------------------------
-- Procedure structure for [Moderator_Detail]
-- ----------------------------
DROP PROCEDURE [Moderator_Detail]
GO

CREATE PROC [Moderator_Detail]
		@Id INT
AS
BEGIN
	SELECT * FROM Moderators WHERE Id=@Id
END

GO

-- ----------------------------
-- Procedure structure for [Moderator_List]
-- ----------------------------
DROP PROCEDURE [Moderator_List]
GO

CREATE PROC [Moderator_List]
		
AS
BEGIN
	SET NOCOUNT ON;	
	
	SELECT 
		Id,
		F.ForumId,
		UserName,
		ForumName 
	FROM Moderators M WITH(NOLOCK) 	INNER JOIN Forums F WITH(NOLOCK) 
	ON M.ForumId=F.ForumId 	 
	ORDER BY ForumId
END

GO

-- ----------------------------
-- Procedure structure for [Moderator_ListByForum]
-- ----------------------------
DROP PROCEDURE [Moderator_ListByForum]
GO

CREATE PROC [Moderator_ListByForum]
		@ForumId INT
AS
BEGIN
	SET NOCOUNT ON;
	SELECT 
		M.ForumId,
		M.AccessMask,
		U.Email,
		U.UserName			
	  FROM Moderators M INNER JOIN Users U ON M.UserId=U.UserId 
	WHERE ForumId=@ForumId;
END

GO

-- ----------------------------
-- Procedure structure for [Moderator_ListByUserName]
-- ----------------------------
DROP PROCEDURE [Moderator_ListByUserName]
GO

CREATE PROC [Moderator_ListByUserName]
		@UserName VARCHAR(64)
AS
BEGIN
	SET NOCOUNT ON;
	SELECT 
		ForumId,
		UserName,
		AccessMask	
	  FROM Moderators WITH(NOLOCK) WHERE UserName=@UserName;
END

GO

-- ----------------------------
-- Procedure structure for [Moderator_Save]
-- ----------------------------
DROP PROCEDURE [Moderator_Save]
GO

CREATE PROC [Moderator_Save]	
	@UserId INT,
	@UserName VARCHAR(64),
	@ForumId INT,	
	@AccessMask VARCHAR(MAX)
AS
BEGIN
	
	IF NOT EXISTS (SELECT 1 FROM Moderators WHERE ForumId=@ForumId AND UserId=@UserId)
	BEGIN
		INSERT INTO Moderators(UserId,UserName,ForumId,AccessMask) 
					VALUES (@UserId,@UserName,@ForumId,@AccessMask)
	END
	ELSE
		BEGIN
			UPDATE Moderators SET 							
							AccessMask = @AccessMask
			WHERE ForumId=@ForumId AND UserId=@UserId
		END
	
END
	

GO

-- ----------------------------
-- Procedure structure for [Notification_Check]
-- ----------------------------
DROP PROCEDURE [Notification_Check]
GO

--[Notification_Check] 1, 1,'2011-09-24 09:30:19'

CREATE PROC [Notification_Check]
			@UserId INT,
			@IsAdmin BIT,
			@LastCheck DATETIME
			
AS
BEGIN
		
	SELECT COUNT (1) FROM Notifications 
	WHERE 
		Created>@LastCheck
		AND [From]<>@UserId
		AND (
				@IsAdmin=1 AND [To]<=0		 
				OR [To]=@UserId		
				OR ForumId IN(SELECT ForumId FROM Subscriptions S WHERE S.UserId=@UserId) 
				OR TopicId IN(SELECT TopicId FROM Subscriptions S WHERE S.UserId=@UserId)
				OR ForumId IN(SELECT ForumId FROM Moderators M WHERE M.UserId=@UserId)
			)	
	
END
		 
	

GO

-- ----------------------------
-- Procedure structure for [Notification_List]
-- ----------------------------
DROP PROCEDURE [Notification_List]
GO

--[Notification_List] 1,0,1,5
		 
CREATE  PROC [Notification_List]
		@UserId INT,
		@IsAdmin BIT,
		@PageIndex INT,
		@PageSize INT,
		@TotalRows INT =0 OUT
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE Users SET LastNotifyCheck=GETUTCDATE() WHERE UserId=@UserId;	
	
	SELECT @TotalRows = COUNT(1) FROM Notifications WHERE [From]<>@UserId AND (@IsAdmin=1 AND [To]<=0) 
					OR [To]=@UserId 
					OR ForumId IN(SELECT ForumId FROM Subscriptions S WHERE s.UserId=@UserId) 
					OR TopicId IN(SELECT TopicId FROM Subscriptions S WHERE s.UserId=@UserId) 
					OR ForumId IN(SELECT ForumId FROM Moderators M WHERE M.UserId=@UserId)
					;
	WITH CTE AS
	(
		SELECT 
			ROW_NUMBER() OVER (ORDER BY Id DESC) AS RowNum,
			Cmd,
			[From],
			[To],
			ForumId,
			TopicId,
			Location,
			Created		
		FROM Notifications 	
		WHERE [From]<>@UserId 
					AND (@IsAdmin=1 AND [To]<=0) 
					OR [To]=@UserId 
					OR ForumId IN(SELECT ForumId FROM Subscriptions S WHERE s.UserId=@UserId) 
					OR TopicId IN(SELECT TopicId FROM Subscriptions S WHERE s.UserId=@UserId)
					OR ForumId IN(SELECT ForumId FROM Moderators M WHERE M.UserId=@UserId)
				 
	) 
	
	SELECT 
	C.*,
	U.UserName,
	U.Avatar,	
	F.ForumName,
	T.TopicName FROM 
	CTE C LEFT JOIN Users U ON C.[From]=U.UserId
	LEFT JOIN Forums F ON C.ForumId =F.ForumId
	LEFT JOIN Topics T ON C.TopicId=T.TopicId	 
					
	WHERE C.RowNum BETWEEN (@PageIndex - 1) * @PageSize + 1 AND @PageIndex * @PageSize	
	
	
END
		

GO

-- ----------------------------
-- Procedure structure for [Notification_Save]
-- ----------------------------
DROP PROCEDURE [Notification_Save]
GO
CREATE PROC [Notification_Save]
		@Cmd SMALLINT,
		@From INT,
		@To INT,
		@ForumId INT,
		@TopicId INT,
		@Location NVARCHAR(200)
AS
BEGIN
	
	INSERT INTO Notifications
	(		
		Cmd,
		[From],
		[To],
		ForumId,
		TopicId,
		Location,		
		Created
	)
	VALUES
	(
		@Cmd,
		@From,
		@To,
		@ForumId,
		@TopicId,
		@Location,
		GETUTCDATE()		
	)
	
END
		

GO

-- ----------------------------
-- Procedure structure for [Register]
-- ----------------------------
DROP PROCEDURE [Register]
GO
CREATE PROC [Register]	
				@UserName NVARCHAR(64),
				@Pwd VARCHAR(145),
				@Email VARCHAR(63),
				@TimeZone FLOAT
				
				
				
AS
DECLARE @RoleId INT

BEGIN
	SET NOCOUNT ON;
	--IF( Len(@ActiveCode)>1)
	--	SET @IsActived=0
	--ELSE
	--	SET @IsActived=1
	SET @RoleId =(SELECT TOP 1 RoleId FROM Roles WHERE IsAutoStart=1)
	INSERT INTO Users(UserName,Pwd,Email,RoleId,TimeZone)
		VALUES(@UserName,@Pwd,@Email,@RoleId,@TimeZone)
	
	
END


GO

-- ----------------------------
-- Procedure structure for [Report_Save]
-- ----------------------------
DROP PROCEDURE [Report_Save]
GO
CREATE PROC [Report_Save]
@TopicId INT,
@UserId INT = 0,
@IP VARCHAR(20),
@Reason NVARCHAR(200)
AS
BEGIN
	INSERT INTO Reports(TopicId,UserId,IP,Reason,Created) VALUES (@TopicId,@UserId,@IP,@Reason,GETUTCDATE());
END 

GO

-- ----------------------------
-- Procedure structure for [Request_Save]
-- ----------------------------
DROP PROCEDURE [Request_Save]
GO

CREATE PROC [Request_Save]
			@Email VARCHAR(50),			
			@ActiveCode VARCHAR(50),
			@ActionType INT
			
AS
BEGIN
	INSERT INTO Requests
	(		
		Email,
		ActiveCode,
		Created,
		ActionType
	)
	VALUES
	(
		@Email	,
		@ActiveCode	,
		GETUTCDATE()	,
		@ActionType	
	)
END


GO

-- ----------------------------
-- Procedure structure for [Role_Delete]
-- ----------------------------
DROP PROCEDURE [Role_Delete]
GO


CREATE PROC [Role_Delete]
		@Ids VARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON
	
	DELETE FROM Roles WHERE RoleId IN (SELECT ID FROM SplitStringToTable(@Ids))
	
END

 

GO

-- ----------------------------
-- Procedure structure for [Role_Detail]
-- ----------------------------
DROP PROCEDURE [Role_Detail]
GO


CREATE PROC [Role_Detail]
		@RoleId INT
AS
BEGIN
	SELECT * FROM Roles WHERE RoleId=@RoleId;
END

 

GO

-- ----------------------------
-- Procedure structure for [Role_List]
-- ----------------------------
DROP PROCEDURE [Role_List]
GO
CREATE PROC [Role_List]
	AS
	BEGIN
		SET NOCOUNT ON;
		SELECT
			R.RoleId,
			R.RoleName,
			R.IsAutoStart,
			R.IsAdmin,			
			R.CssClass,
			R.AccessMask,
			R.AllowLogin
		FROM
			Roles R
	END

GO

-- ----------------------------
-- Procedure structure for [Role_Save]
-- ----------------------------
DROP PROCEDURE [Role_Save]
GO

CREATE PROC [Role_Save]
			@RoleId INT=0,
			@RoleName NVARCHAR(100),
			@IsAutoStart BIT,
			@IsAdmin BIT,			
			@IsSuperModerator BIT,
			@AllowLogin BIT,
			@CssClass VARCHAR(20),
			@AccessMask VARCHAR(4000)
AS
BEGIN
	
	IF @IsAutoStart=1 AND @RoleId=0
		UPDATE Roles SET IsAutoStart=0 WHERE IsAutoStart=1
	
	
	IF(@RoleId>0)
	BEGIN
		UPDATE Roles
		SET
			
			RoleName = @RoleName,
			IsAutoStart = @IsAutoStart,
			AllowLogin=@AllowLogin,
			IsAdmin = @IsAdmin,
			IsSuperModerator=@IsSuperModerator,
			CssClass = @CssClass,
			AccessMask = @AccessMask
		WHERE RoleId=@RoleId
			
	END
	ELSE
		BEGIN
			INSERT INTO Roles
			(
				
				RoleName,
				IsAutoStart,
				IsAdmin,
				IsSuperModerator,
				AllowLogin,
				CssClass,
				AccessMask
				
			)
			VALUES
			(
			
				@RoleName,
				@IsAutoStart,
				@IsAdmin,
				@IsSuperModerator,
				@AllowLogin,
				@CssClass,
				@AccessMask
			)
		END
END
			
			

GO

-- ----------------------------
-- Procedure structure for [Smile_List]
-- ----------------------------
DROP PROCEDURE [Smile_List]
GO
CREATE PROC [Smile_List]
AS
BEGIN
	SET NOCOUNT ON;
	SELECT * FROM Smiles WITH(NOLOCK);
END

GO

-- ----------------------------
-- Procedure structure for [sp_alterdiagram]
-- ----------------------------
DROP PROCEDURE [sp_alterdiagram]
GO

	CREATE PROCEDURE [sp_alterdiagram]
	(
		@diagramname 	sysname,
		@owner_id	int	= null,
		@version 	int,
		@definition 	varbinary(max)
	)
	WITH EXECUTE AS 'dbo'
	AS
	BEGIN
		set nocount on
	
		declare @theId 			int
		declare @retval 		int
		declare @IsDbo 			int
		
		declare @UIDFound 		int
		declare @DiagId			int
		declare @ShouldChangeUID	int
	
		if(@diagramname is null)
		begin
			RAISERROR ('Invalid ARG', 16, 1)
			return -1
		end
	
		execute as caller;
		select @theId = DATABASE_PRINCIPAL_ID();	 
		select @IsDbo = IS_MEMBER(N'db_owner'); 
		if(@owner_id is null)
			select @owner_id = @theId;
		revert;
	
		select @ShouldChangeUID = 0
		select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname 
		
		if(@DiagId IS NULL or (@IsDbo = 0 and @theId <> @UIDFound))
		begin
			RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1);
			return -3
		end
	
		if(@IsDbo <> 0)
		begin
			if(@UIDFound is null or USER_NAME(@UIDFound) is null) -- invalid principal_id
			begin
				select @ShouldChangeUID = 1 ;
			end
		end

		-- update dds data			
		update dbo.sysdiagrams set definition = @definition where diagram_id = @DiagId ;

		-- change owner
		if(@ShouldChangeUID = 1)
			update dbo.sysdiagrams set principal_id = @theId where diagram_id = @DiagId ;

		-- update dds version
		if(@version is not null)
			update dbo.sysdiagrams set version = @version where diagram_id = @DiagId ;

		return 0
	END
	

GO

-- ----------------------------
-- Procedure structure for [sp_creatediagram]
-- ----------------------------
DROP PROCEDURE [sp_creatediagram]
GO

	CREATE PROCEDURE [sp_creatediagram]
	(
		@diagramname 	sysname,
		@owner_id		int	= null, 	
		@version 		int,
		@definition 	varbinary(max)
	)
	WITH EXECUTE AS 'dbo'
	AS
	BEGIN
		set nocount on
	
		declare @theId int
		declare @retval int
		declare @IsDbo	int
		declare @userName sysname
		if(@version is null or @diagramname is null)
		begin
			RAISERROR (N'E_INVALIDARG', 16, 1);
			return -1
		end
	
		execute as caller;
		select @theId = DATABASE_PRINCIPAL_ID(); 
		select @IsDbo = IS_MEMBER(N'db_owner');
		revert; 
		
		if @owner_id is null
		begin
			select @owner_id = @theId;
		end
		else
		begin
			if @theId <> @owner_id
			begin
				if @IsDbo = 0
				begin
					RAISERROR (N'E_INVALIDARG', 16, 1);
					return -1
				end
				select @theId = @owner_id
			end
		end
		-- next 2 line only for test, will be removed after define name unique
		if EXISTS(select diagram_id from dbo.sysdiagrams where principal_id = @theId and name = @diagramname)
		begin
			RAISERROR ('The name is already used.', 16, 1);
			return -2
		end
	
		insert into dbo.sysdiagrams(name, principal_id , version, definition)
				VALUES(@diagramname, @theId, @version, @definition) ;
		
		select @retval = @@IDENTITY 
		return @retval
	END
	

GO

-- ----------------------------
-- Procedure structure for [sp_dropdiagram]
-- ----------------------------
DROP PROCEDURE [sp_dropdiagram]
GO

	CREATE PROCEDURE [sp_dropdiagram]
	(
		@diagramname 	sysname,
		@owner_id	int	= null
	)
	WITH EXECUTE AS 'dbo'
	AS
	BEGIN
		set nocount on
		declare @theId 			int
		declare @IsDbo 			int
		
		declare @UIDFound 		int
		declare @DiagId			int
	
		if(@diagramname is null)
		begin
			RAISERROR ('Invalid value', 16, 1);
			return -1
		end
	
		EXECUTE AS CALLER;
		select @theId = DATABASE_PRINCIPAL_ID();
		select @IsDbo = IS_MEMBER(N'db_owner'); 
		if(@owner_id is null)
			select @owner_id = @theId;
		REVERT; 
		
		select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname 
		if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId))
		begin
			RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1)
			return -3
		end
	
		delete from dbo.sysdiagrams where diagram_id = @DiagId;
	
		return 0;
	END
	

GO

-- ----------------------------
-- Procedure structure for [sp_helpdiagramdefinition]
-- ----------------------------
DROP PROCEDURE [sp_helpdiagramdefinition]
GO

	CREATE PROCEDURE [sp_helpdiagramdefinition]
	(
		@diagramname 	sysname,
		@owner_id	int	= null 		
	)
	WITH EXECUTE AS N'dbo'
	AS
	BEGIN
		set nocount on

		declare @theId 		int
		declare @IsDbo 		int
		declare @DiagId		int
		declare @UIDFound	int
	
		if(@diagramname is null)
		begin
			RAISERROR (N'E_INVALIDARG', 16, 1);
			return -1
		end
	
		execute as caller;
		select @theId = DATABASE_PRINCIPAL_ID();
		select @IsDbo = IS_MEMBER(N'db_owner');
		if(@owner_id is null)
			select @owner_id = @theId;
		revert; 
	
		select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname;
		if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId ))
		begin
			RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1);
			return -3
		end

		select version, definition FROM dbo.sysdiagrams where diagram_id = @DiagId ; 
		return 0
	END
	

GO

-- ----------------------------
-- Procedure structure for [sp_helpdiagrams]
-- ----------------------------
DROP PROCEDURE [sp_helpdiagrams]
GO

	CREATE PROCEDURE [sp_helpdiagrams]
	(
		@diagramname sysname = NULL,
		@owner_id int = NULL
	)
	WITH EXECUTE AS N'dbo'
	AS
	BEGIN
		DECLARE @user sysname
		DECLARE @dboLogin bit
		EXECUTE AS CALLER;
			SET @user = USER_NAME();
			SET @dboLogin = CONVERT(bit,IS_MEMBER('db_owner'));
		REVERT;
		SELECT
			[Database] = DB_NAME(),
			[Name] = name,
			[ID] = diagram_id,
			[Owner] = USER_NAME(principal_id),
			[OwnerID] = principal_id
		FROM
			sysdiagrams
		WHERE
			(@dboLogin = 1 OR USER_NAME(principal_id) = @user) AND
			(@diagramname IS NULL OR name = @diagramname) AND
			(@owner_id IS NULL OR principal_id = @owner_id)
		ORDER BY
			4, 5, 1
	END
	

GO

-- ----------------------------
-- Procedure structure for [sp_renamediagram]
-- ----------------------------
DROP PROCEDURE [sp_renamediagram]
GO

	CREATE PROCEDURE [sp_renamediagram]
	(
		@diagramname 		sysname,
		@owner_id		int	= null,
		@new_diagramname	sysname
	
	)
	WITH EXECUTE AS 'dbo'
	AS
	BEGIN
		set nocount on
		declare @theId 			int
		declare @IsDbo 			int
		
		declare @UIDFound 		int
		declare @DiagId			int
		declare @DiagIdTarg		int
		declare @u_name			sysname
		if((@diagramname is null) or (@new_diagramname is null))
		begin
			RAISERROR ('Invalid value', 16, 1);
			return -1
		end
	
		EXECUTE AS CALLER;
		select @theId = DATABASE_PRINCIPAL_ID();
		select @IsDbo = IS_MEMBER(N'db_owner'); 
		if(@owner_id is null)
			select @owner_id = @theId;
		REVERT;
	
		select @u_name = USER_NAME(@owner_id)
	
		select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname 
		if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId))
		begin
			RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1)
			return -3
		end
	
		-- if((@u_name is not null) and (@new_diagramname = @diagramname))	-- nothing will change
		--	return 0;
	
		if(@u_name is null)
			select @DiagIdTarg = diagram_id from dbo.sysdiagrams where principal_id = @theId and name = @new_diagramname
		else
			select @DiagIdTarg = diagram_id from dbo.sysdiagrams where principal_id = @owner_id and name = @new_diagramname
	
		if((@DiagIdTarg is not null) and  @DiagId <> @DiagIdTarg)
		begin
			RAISERROR ('The name is already used.', 16, 1);
			return -2
		end		
	
		if(@u_name is null)
			update dbo.sysdiagrams set [name] = @new_diagramname, principal_id = @theId where diagram_id = @DiagId
		else
			update dbo.sysdiagrams set [name] = @new_diagramname where diagram_id = @DiagId
		return 0
	END
	

GO

-- ----------------------------
-- Procedure structure for [sp_upgraddiagrams]
-- ----------------------------
DROP PROCEDURE [sp_upgraddiagrams]
GO

	CREATE PROCEDURE [sp_upgraddiagrams]
	AS
	BEGIN
		IF OBJECT_ID(N'dbo.sysdiagrams') IS NOT NULL
			return 0;
	
		CREATE TABLE dbo.sysdiagrams
		(
			name sysname NOT NULL,
			principal_id int NOT NULL,	-- we may change it to varbinary(85)
			diagram_id int PRIMARY KEY IDENTITY,
			version int,
	
			definition varbinary(max)
			CONSTRAINT UK_principal_name UNIQUE
			(
				principal_id,
				name
			)
		);


		/* Add this if we need to have some form of extended properties for diagrams */
		/*
		IF OBJECT_ID(N'dbo.sysdiagram_properties') IS NULL
		BEGIN
			CREATE TABLE dbo.sysdiagram_properties
			(
				diagram_id int,
				name sysname,
				value varbinary(max) NOT NULL
			)
		END
		*/

		IF OBJECT_ID(N'dbo.dtproperties') IS NOT NULL
		begin
			insert into dbo.sysdiagrams
			(
				[name],
				[principal_id],
				[version],
				[definition]
			)
			select	 
				convert(sysname, dgnm.[uvalue]),
				DATABASE_PRINCIPAL_ID(N'dbo'),			-- will change to the sid of sa
				0,							-- zero for old format, dgdef.[version],
				dgdef.[lvalue]
			from dbo.[dtproperties] dgnm
				inner join dbo.[dtproperties] dggd on dggd.[property] = 'DtgSchemaGUID' and dggd.[objectid] = dgnm.[objectid]	
				inner join dbo.[dtproperties] dgdef on dgdef.[property] = 'DtgSchemaDATA' and dgdef.[objectid] = dgnm.[objectid]
				
			where dgnm.[property] = 'DtgSchemaNAME' and dggd.[uvalue] like N'_EA3E6268-D998-11CE-9454-00AA00A3F36E_' 
			return 2;
		end
		return 1;
	END
	

GO

-- ----------------------------
-- Procedure structure for [Statistical_ListMaxUsersOnline]
-- ----------------------------
DROP PROCEDURE [Statistical_ListMaxUsersOnline]
GO

CREATE  PROC [Statistical_ListMaxUsersOnline]
AS
BEGIN
	SET NOCOUNT ON;
	SELECT TOP 1 TotalUsersOnline,Created  FROM Statisticals ORDER BY TotalUsersOnline DESC;
	
END

GO

-- ----------------------------
-- Procedure structure for [Statistical_Update]
-- ----------------------------
DROP PROCEDURE [Statistical_Update]
GO

CREATE PROCEDURE [Statistical_Update] 
@PastMinutes INT
AS
DECLARE @TotalUserOnline INT
BEGIN
	SET NOCOUNT ON;
	 IF (SELECT COUNT(1) FROM Statisticals)<=1
	 BEGIN
	 	INSERT INTO Statisticals(Created) VALUES (GETUTCDATE())
	 END
	 
	 SET @TotalUserOnline = (SELECT COUNT(1) FROM Activities WITH(NOLOCK) WHERE (DATEADD(Mi, @PastMinutes, LastAction)> GETUTCDATE()))
	 
	 UPDATE Statisticals 
	 SET 
			Created=GETUTCDATE(),
			TotalUsersOnline=@TotalUserOnline,
			Guests=(SELECT COUNT(1) FROM Activities WITH(NOLOCK) WHERE (UserId IS NULL OR UserId=0) AND (DATEADD(Mi, @PastMinutes, LastAction)> GETUTCDATE())),
			TotalUsers=(SELECT COUNT(1) FROM Users WITH(NOLOCK)),
			TotalTopics = (SELECT COUNT(1) FROM Topics WITH(NOLOCK) WHERE IsDeleted=0 AND ReplyTo IS NULL ),
			TotalPosts=(SELECT COUNT(1) FROM Topics WITH(NOLOCK) WHERE IsDeleted=0 AND ReplyTo >0),
			NewPostsInPast24Hours=(SELECT COUNT(1) FROM Topics WITH(NOLOCK) WHERE IsDeleted=0 AND ReplyTo >0 AND (DATEADD(hh, 24, Created)> GETUTCDATE())),
			NewTopicsInPast24Hours=(SELECT COUNT(1) FROM Topics WITH(NOLOCK) WHERE IsDeleted=0 AND ReplyTo IS NULL AND (DATEADD(hh, 24, Created)> GETUTCDATE())),
			NewUsersInPast24Hours=(SELECT COUNT(1) FROM Users WITH(NOLOCK) WHERE (DATEADD(hh, 24, RegDate)> GETUTCDATE())),
			NewestUserID=(SELECT TOP 1  UserName FROM Users WITH(NOLOCK) ORDER BY RegDate DESC)
	 WHERE 
		 TotalUsersOnline<=@TotalUserOnline OR (ID =(SELECT TOP 1 ID from Statisticals ORDER BY TotalUsersOnline))
		
	
	SELECT TOP 1 * FROM Statisticals ORDER BY Created DESC
	
	--SELECT TOP 1 TotalUsersOnline,Created  FROM Nasa8xForum_Statistics	ORDER BY TotalUsersOnline DESC
		
	 
END


GO

-- ----------------------------
-- Procedure structure for [Statistical_UpdateMaxUsersOnline]
-- ----------------------------
DROP PROCEDURE [Statistical_UpdateMaxUsersOnline]
GO

CREATE PROC [Statistical_UpdateMaxUsersOnline]
				@PastMinutes INT
AS
DECLARE @TotalUserOnline INT
BEGIN
	SET @TotalUserOnline = (SELECT COUNT(1) FROM Activities WHERE (DATEADD(Mi, @PastMinutes, LastAction)> GETUTCDATE()))
	UPDATE Statisticals 
	 SET 
			Created=GETUTCDATE(),
			TotalUsersOnline=@TotalUserOnline
	WHERE
		TotalUsersOnline<@TotalUserOnline
END

GO

-- ----------------------------
-- Procedure structure for [Subscription_List]
-- ----------------------------
DROP PROCEDURE [Subscription_List]
GO


CREATE PROC [Subscription_List]
							@UserId INT,
							@Type SMALLINT=1, --0: forums; 1: topics
							@PageIndex INT=1,
							@PageSize INT,
							@TotalRows INT=0 OUT
	AS
	
	BEGIN
		SET NOCOUNT ON
		
		IF @Type=1		
		BEGIN
			SELECT @TotalRows= COUNT(1) FROM Subscriptions WHERE UserId=@UserId AND TopicId IS NOT NULL;						
			
			WITH CTE AS (
				SELECT 
				ROW_NUMBER() OVER (ORDER BY Created) AS RowNum,	
				Id,				
				TopicId,
				NotificationType,			
				Created,
				LastEmail	
						
				FROM Subscriptions WITH(NOLOCK)
				WHERE UserId=@UserId AND TopicId IS NOT NULL		
				
			)
			SELECT C.*, T.TopicName	FROM CTE C INNER JOIN Topics T WITH(NOLOCK) ON C.TopicId=T.TopicId
			WHERE C.RowNum BETWEEN (@PageIndex - 1) * @PageSize + 1 AND @PageIndex * @PageSize;
			
		END
		ELSE
		BEGIN
			SELECT @TotalRows= COUNT(1) FROM Subscriptions WHERE UserId=@UserId AND ForumId IS NOT NULL;
			
			WITH CTE AS (
				SELECT 
				ROW_NUMBER() OVER (ORDER BY Created) AS RowNum,
				Id,					
				ForumId,	
				NotificationType,		
				Created,
				LastEmail	
						
				FROM Subscriptions WITH(NOLOCK)
				WHERE UserId=@UserId AND ForumId IS NOT NULL		
				
			)
			SELECT C.*, F.ForumName	FROM CTE C INNER JOIN Forums F WITH(NOLOCK) ON C.ForumId=F.ForumId
			WHERE C.RowNum BETWEEN (@PageIndex - 1) * @PageSize + 1 AND @PageIndex * @PageSize;
				
			END
		
		
		
	END

GO

-- ----------------------------
-- Procedure structure for [Subscription_Save]
-- ----------------------------
DROP PROCEDURE [Subscription_Save]
GO

CREATE PROC [Subscription_Save]
					@Id INT = NULL,
					@ForumId INT =NULL,
					@TopicId INT =NULL,
					@UserId INT,
					@NotificationType SMALLINT
					
					
AS
BEGIN
	
	
	IF @ForumId<=0
		SET @ForumId=NULL
		
		IF @TopicId<=0
		SET @TopicId=NULL
		
			
					
	IF @Id<=0 AND @NotificationType<>-1
		BEGIN
			
			IF NOT EXISTS (SELECT 1 FROM Subscriptions WITH(NOLOCK) WHERE UserId=@UserId AND ((@ForumId >0 AND ForumId=@ForumId) OR (@TopicId>0 AND TopicId=@TopicId) ))			
			INSERT INTO Subscriptions
			(
				ForumId,
				TopicId,
				UserId,
				NotificationType,
				Created
				
			)
			VALUES
			(
				@ForumId,
				@TopicId,
				@UserId,
				@NotificationType,
				GETUTCDATE()
			)	
		END
	ELSE
		
			IF @NotificationType=-1			
			BEGIN
				DELETE FROM Subscriptions 
				WHERE Id=@Id AND UserId=@UserId
				PRINT @NotificationType
			END	
				
				
			ELSE
				UPDATE Subscriptions
						SET NotificationType = @NotificationType WHERE Id=@Id AND UserId=@UserId	
		
				
		
END		

GO

-- ----------------------------
-- Procedure structure for [Thread_List]
-- ----------------------------
DROP PROCEDURE [Thread_List]
GO

CREATE PROCEDURE [Thread_List] 
				@IsModerator BIT=0,
				@TopicId INT,				
				@PageIndex INT=1,
				@PageSize INT =25,				
				@TotalRows INT =0 OUT				
				
AS
BEGIN
	SET NOCOUNT ON	
	
	UPDATE Topics SET [Views] = [Views]+1 WHERE TopicId=@TopicId;
	
	SELECT  @TotalRows = COUNT(1) FROM Topics T WHERE  (@IsModerator=1 OR  T.IsDeleted=0) AND (@IsModerator=1 OR T.IsApproved=1) AND (T.ReplyTo=@TopicId OR T.TopicId=@TopicId);
	
	WITH CTE AS (
		SELECT 
		ROW_NUMBER() OVER (ORDER BY TopicId) AS RowNum,		
		T.UserId,		
		T.TopicId,
		T.TopicName,
		T.ForumId,		
		T.Icon,
		T.IsApproved,
		T.IsDeleted,
		T.ReplyTo,
		T.[Message],		
		T.IsLocked,		
		T.Created,		
		T.LastEdited,
		T.EditedBy,
		T.Reason,
		T.IP
		FROM Topics T
		WHERE (@IsModerator=1 OR T.IsDeleted=0) AND (@IsModerator=1 OR T.IsApproved=1) AND (T.ReplyTo=@TopicId OR T.TopicId=@TopicId)	
		
		
	)	
	SELECT 
		C.*,
		R.RoleName,
		R.CssClass,		
		U.[Signature],
		U.Avatar,
		U.TotalVisits,
		U.RegDate,
		U.Email,
		U.Sex,
		U.IsBanned,
		U.UserName,		
		U.TotalPosts	
	
	 FROM CTE C INNER JOIN Users U ON C.UserId=U.UserId  
				INNER JOIN Roles R ON U.RoleId=R.RoleId
	WHERE C.RowNum BETWEEN (@PageIndex - 1) * @PageSize + 1 AND @PageIndex * @PageSize
	ORDER BY TopicId;
			
	
END

GO

-- ----------------------------
-- Procedure structure for [Topic_Delete]
-- ----------------------------
DROP PROCEDURE [Topic_Delete]
GO
CREATE PROC [Topic_Delete]
		@Ids VARCHAR(MAX),
		@ForumId INT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @TBL_IDS TABLE([Id] INT);
	DECLARE @Posts INT,@Topics INT;

	INSERT INTO @TBL_IDS(Id) SELECT TopicId FROM Topics T WITH(NOLOCK) WHERE T.TopicId IN (SELECT ID FROM SplitStringToTable(@Ids)) OR ReplyTo IN (SELECT ID FROM SplitStringToTable(@Ids)) ;
	
	SELECT @Topics=-(SELECT COUNT(1) FROM Topics T WITH(NOLOCK) INNER JOIN @TBL_IDS T2 ON T.TopicId=T2.Id WHERE T.ReplyTo IS NULL),
				@Posts = -(SELECT COUNT(1) FROM Topics T WITH(NOLOCK) INNER JOIN @TBL_IDS T2 ON T.TopicId=T2.Id WHERE T.ReplyTo>0)
		
	DELETE FROM Topics WHERE TopicId IN (SELECT Id FROM @TBL_IDS);							
	
	EXEC Forum_CalcLastPost @ForumId,@Topics,@Posts;	
	
	EXEC User_CalcTotalPostsByTopicIds @Ids;

	DELETE Notifications WHERE TopicId IN (SELECT Id FROM @TBL_IDS)
	
END


GO

-- ----------------------------
-- Procedure structure for [Topic_Detail]
-- ----------------------------
DROP PROCEDURE [Topic_Detail]
GO


CREATE PROC [Topic_Detail]
			@TopicId INT
			
AS
BEGIN
	SET NOCOUNT ON;
	SELECT
		TopicId,
		TopicName,
		ForumId,
		PollId,
		Icon,
		[Message],
		ReplyTo,
		Priority,
		PriorityExpires,
		IsApproved,
		Views,		
		IsLocked,
		RemoteUrl,
		Created,	
		IP,
		Tags,
		U.UserId,
		U.UserName,
		U.RegDate,
		U.TotalPosts,
		U.TotalVisits,
		R.RoleName,
		R.CssClass
	FROM
		Topics T INNER JOIN Users U ON T.UserId=U.UserId
		INNER JOIN Roles R ON R.RoleId=U.RoleId
	WHERE  TopicId=@TopicId
	
END

GO

-- ----------------------------
-- Procedure structure for [Topic_Feed]
-- ----------------------------
DROP PROCEDURE [Topic_Feed]
GO


CREATE PROC [Topic_Feed]
		@ForumId INT,
		@PageSize INT
AS
BEGIN
	SET NOCOUNT ON;
	
	IF(@ForumId>0)
	BEGIN
		-- Parent to childs	
		WITH CTE (ForumId,ParentId) AS
		(
		   -- Base case
		   SELECT
			  ForumId,
			  ParentId		     
			 
		   FROM Forums F WITH(NOLOCK)
		   WHERE ParentId =@ForumId	OR ForumId=@ForumId AND F.AllowRoles IS NULL
		   UNION ALL
		   -- Recursive step
		   SELECT
			  F.ForumId,
			  F.ParentId		 
			
		   FROM Forums F WITH(NOLOCK)
			  INNER JOIN CTE C ON C.ForumId = F.ParentId AND F.AllowRoles IS NULL
		)
		SELECT TOP(@PageSize) 			
				T.TopicId,
				T.ForumId,
				T.TopicName,
				T.[Message],
				T.Created	
			FROM Topics T WITH(NOLOCK) INNER JOIN CTE F ON T.ForumId=F.ForumId
			WHERE IsApproved=1 AND ReplyTo IS NULL 
		ORDER BY TopicId DESC	
			OPTION (MAXRECURSION 0);
			
		END
	ELSE
	BEGIN
		
		SELECT TOP (@PageSize) 
					T.TopicId,
					T.ForumId,
					T.TopicName,
					T.[Message],
					T.Created						
					FROM Topics T INNER JOIN Forums f ON T.ForumId=f.ForumId
					WHERE T.ReplyTo IS NULL AND f.AllowRoles IS NULL
		ORDER BY T.TopicId DESC
			
		END
	
	
	
END

GO

-- ----------------------------
-- Procedure structure for [Topic_FindPost]
-- ----------------------------
DROP PROCEDURE [Topic_FindPost]
GO

CREATE PROC [Topic_FindPost]  
				@TopicId INT,
				@Action VARCHAR(10),
				@TotalRows INT = 0 OUT 
								
AS
	
	BEGIN
				
		SET NOCOUNT ON
		
		
		IF LEN(@Action)>1 
			BEGIN
				
				IF(UPPER(@Action)='NEXT')
					BEGIN
						SELECT Top(1) TopicName, T.ForumId,TopicId,ReplyTo,T.RemoteUrl
						FROM Topics T WITH(NOLOCK) 
						WHERE T.TopicId>@TopicId AND ReplyTo IS NULL AND ForumId=(SELECT ForumId FROM Topics WITH(NOLOCK) WHERE TopicId=@TopicId)
						ORDER BY T.TopicId ASC
					END
				ELSE
					BEGIN
						SELECT Top(1) TopicName, T.ForumId,TopicId,ReplyTo,T.RemoteUrl
						FROM Topics T WITH(NOLOCK)
						WHERE T.TopicId<@TopicId AND ReplyTo IS NULL AND ForumId=(SELECT ForumId FROM Topics WITH(NOLOCK) WHERE TopicId=@TopicId)
						ORDER BY T.TopicId DESC
					END			
				
				SELECT @TotalRows=1;
			END
		ELSE
			BEGIN
				SELECT @TotalRows=COUNT(1) FROM Topics WHERE IsApproved=1 AND TopicId=@TopicId AND 
				ReplyTo IN (SELECT ReplyTo FROM Topics  
							WHERE ReplyTo=@TopicId 
								AND IsDeleted=0						
								AND TopicId<=@TopicId
							);
				
				SELECT ISNULL(T.TopicName,T2.TopicName) AS TopicName,
				T.ForumId,
				T.TopicId,
				T.ReplyTo,
				T.RemoteUrl
				FROM Topics T LEFT JOIN Topics T2 ON T.ReplyTo=T2.TopicId
				WHERE (T.TopicId=@TopicId AND T.IsDeleted=0 AND T.IsApproved=1)
			END	
		
		
	END

GO

-- ----------------------------
-- Procedure structure for [Topic_GetNewPosts]
-- ----------------------------
DROP PROCEDURE [Topic_GetNewPosts]
GO


CREATE PROC [Topic_GetNewPosts]
						@ResultType SMALLINT = 0,--0: Thread, 1:Posts
						@MaxResults INT = 500,
						@LastSearch DATETIME
						
AS
	BEGIN		
		
		SET NOCOUNT ON
		
		;WITH CTE AS
			(
				SELECT
					T.TopicId,	
					MAX(T2.TopicId) LastPostId,
					COUNT(T2.TopicId) Replies	
				FROM Topics T LEFT JOIN Topics T2 ON T.TopicId=T2.ReplyTo
				WHERE T.IsApproved=1 AND T.Created>@LastSearch 
				GROUP BY T.TopicId
			)
		SELECT TOP (@MaxResults) 
			T.TopicId,
			T.TopicName,						
			T.Created,
			T.Icon,		
			T.XForumId,
			T.ForumId,
			T.PollId,			
			T.IsLocked,
			T.IsApproved,
			T.IsDeleted,
			T.Priority,
			T.RemoteUrl,
			T.Views,
			T.UserName,
			T.UserId,
			C.LastPostId,
			C.Replies,			
			T2.Created as LastPosted,
			T2.UserId as LastUserId,
			T2.UserName as LastUserName,
			F.ForumName 
			FROM CTE C INNER JOIN Topics T ON C.TopicId=T.TopicId
						INNER JOIN Forums F ON T.ForumId=F.ForumId
						LEFT JOIN Topics T2 ON C.LastPostId=T2.TopicId
			WHERE (@ResultType=0 AND T.ReplyTo IS NULL) OR @ResultType=1
			ORDER BY LastPosted DESC
		
		
		END

GO

-- ----------------------------
-- Procedure structure for [Topic_List]
-- ----------------------------
DROP PROCEDURE [Topic_List]
GO

--[Topic_List] 2,0,1,25,'LASTPOST','DESC'

CREATE PROCEDURE [Topic_List] 
			( 
				@ForumId INT,				
				@IsModerator BIT=0,				
				@PageIndex INT =1,
				@PageSize INT = 25,
				@OrderBy VARCHAR(20),
				@Dir VARCHAR(4),
				@TotalRows INT =0 OUT
			)
AS
BEGIN
		SET NOCOUNT ON;
	
		UPDATE Topics SET Priority=0 WHERE Priority>0 AND PriorityExpires IS NOT NULL AND PriorityExpires<GETUTCDATE() ;

		SELECT @TotalRows = COUNT(1) FROM Topics T WHERE (@IsModerator=1 OR T.IsApproved=1) 
						AND T.ReplyTo IS NULL 
						AND (T.ForumId=@ForumId OR (T.XForumId=@ForumId AND (T.RedirectExpires IS NULL OR T.RedirectExpires>GETUTCDATE())))		

			;WITH CTE AS
			(
				SELECT 				
					T.TopicId,	
					MAX(T2.TopicId) LastPostId,
					COUNT(T2.TopicId) Replies	
				FROM Topics T LEFT JOIN Topics T2 ON T.TopicId=T2.ReplyTo
				WHERE (@IsModerator=1 OR T.IsApproved=1) 
						AND T.ReplyTo IS NULL 
						AND (T.ForumId=@ForumId OR (T.XForumId=@ForumId AND (T.RedirectExpires IS NULL OR T.RedirectExpires>GETUTCDATE())))		

				GROUP BY T.TopicId
			),
			CTE2 AS
			(
				SELECT 
					ROW_NUMBER() OVER 
					(
						ORDER BY T.Priority DESC,
							CASE UPPER(@Dir) WHEN 'DESC' THEN
								CASE UPPER(@OrderBy)							
									WHEN 'CREATED' THEN T.Created
									WHEN 'REPLY' THEN C.Replies
									WHEN 'VIEWS' THEN T.Views
									WHEN 'LASTPOST' THEN ISNULL(T2.Created,T.Created)
									WHEN 'EDITED' THEN T.LastEdited
									WHEN 'TITLE' THEN ISNULL(T.TopicName,' ')
									ELSE T.TopicId							
								END 						
							END DESC,
							CASE UPPER(@Dir) WHEN 'ASC' THEN
								CASE UPPER(@OrderBy)
									WHEN 'CREATED' THEN T.Created
									WHEN 'REPLY' THEN C.Replies
									WHEN 'VIEWS' THEN T.Views
									WHEN 'LASTPOST' THEN ISNULL(T2.Created,T.Created)
									WHEN 'EDITED' THEN T.LastEdited
									WHEN 'TITLE' THEN  ISNULL(T.TopicName,' ')
									ELSE T.TopicId							
								END						
							END ASC					
				 
					) AS RowNum,			
			T.TopicId,
			T.TopicName,			
			T.Icon,		
			T.xForumId,
			T.PollId,			
			T.IsLocked,
			T.IsApproved,			
			T.Priority,
			T.RemoteUrl,	
			T.ReplyTo,
			T.UserName,
			T.UserId,
			T.Created,
			T.LastEdited,
			T.Views,
			C.Replies,
			C.LastPostId,
			T2.Created as LastPosted,
			T2.UserId as LastUserId,
			T2.UserName as LastUserName
			FROM CTE C INNER JOIN Topics T ON C.TopicId=T.TopicId
					LEFT JOIN Topics T2 ON C.LastPostId=T2.TopicId
			)

			SELECT * FROM CTE2 C
			WHERE C.RowNum BETWEEN (@PageIndex - 1) * @PageSize + 1 AND @PageIndex * @PageSize
			
END

GO

-- ----------------------------
-- Procedure structure for [Topic_Mod_Delete]
-- ----------------------------
DROP PROCEDURE [Topic_Mod_Delete]
GO
CREATE PROC [Topic_Mod_Delete]
		@Ids VARCHAR(MAX),
		@ForumId INT,
		@IsTopic BIT,
		@IsPost BIT
AS
BEGIN
	SET NOCOUNT ON;	
	DECLARE @TBL_IDS TABLE([Id] INT);	
	DECLARE @Posts INT,@Topics INT;
	INSERT INTO @TBL_IDS(Id) SELECT Id FROM SplitStringToTable(@Ids);	

	SELECT @Topics=-(SELECT COUNT(1) FROM Topics T WITH(NOLOCK) INNER JOIN @TBL_IDS T2 ON T.TopicId=T2.Id WHERE @IsTopic=1 AND T.ReplyTo IS NULL),
				@Posts = -(SELECT COUNT(1) FROM Topics T WITH(NOLOCK) INNER JOIN @TBL_IDS T2 ON T.TopicId=T2.Id WHERE @IsPost=1 AND T.ReplyTo>0)
	
	DELETE FROM Topics 	WHERE ((@IsTopic=1 AND ReplyTo IS NULL) OR (@IsPost=1 AND ReplyTo>0)) AND TopicId IN(SELECT Id FROM @TBL_IDS); 
	
	-- Update forum counter
	EXEC Forum_CalcLastPost @ForumId,@Topics,@Posts;
	
	--Update Users totalposts
	
	EXEC User_CalcTotalPostsByTopicIds @Ids;
	
	
END		 


GO

-- ----------------------------
-- Procedure structure for [Topic_Mod_MoveToForum]
-- ----------------------------
DROP PROCEDURE [Topic_Mod_MoveToForum]
GO
CREATE PROC [Topic_Mod_MoveToForum]
		@Ids VARCHAR(MAX),
		@ForumId INT,
		@AllowRedirect BIT,
		@Expires DATETIME 
		 
AS
BEGIN
	SET NOCOUNT ON;

	IF @ForumId>0
	BEGIN
		IF @AllowRedirect=1
		BEGIN
			IF @Expires<=GETUTCDATE()
				SET @Expires=NULL 
			
			UPDATE Topics
			SET	 ForumId = @ForumId, XForumId=ForumId , RedirectExpires=@Expires
			WHERE ForumId<>@ForumId AND TopicId IN (SELECT ID FROM SplitStringToTable(@Ids));
		END
			
		ELSE
			UPDATE Topics
			SET ForumId = @ForumId 
			WHERE ForumId<>@ForumId AND TopicId IN (SELECT ID FROM SplitStringToTable(@Ids));				
			
	END
	ELSE -- UnMove
		UPDATE Topics SET ForumId = xForumId,XForumId=NULL, RedirectExpires=NULL 
		WHERE TopicId IN (SELECT ID FROM SplitStringToTable(@Ids));		
	
END


GO

-- ----------------------------
-- Procedure structure for [Topic_Mod_Queue]
-- ----------------------------
DROP PROCEDURE [Topic_Mod_Queue]
GO
CREATE PROC [Topic_Mod_Queue]
		@Ids VARCHAR(MAX),
		@ForumId INT,
		@IsTopic BIT,
		@IsPost BIT,
		@Val BIT -- 0 or 1
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @TBL_IDS TABLE([Id] INT);
	
	DECLARE @Posts INT,@Topics INT;
	
	INSERT INTO @TBL_IDS(Id) SELECT TopicId FROM Topics T WITH(NOLOCK) WHERE T.TopicId IN (SELECT ID FROM SplitStringToTable(@Ids));
	
	
	SELECT @Topics=(SELECT COUNT(1) FROM Topics T WITH(NOLOCK) INNER JOIN @TBL_IDS T2 ON T.TopicId=T2.Id WHERE @IsTopic=1 AND T.ReplyTo IS NULL),
				@Posts = (SELECT COUNT(1) FROM Topics T WITH(NOLOCK) INNER JOIN @TBL_IDS T2 ON T.TopicId=T2.Id WHERE @IsPost=1 AND T.ReplyTo>0)
	
	UPDATE Topics SET IsApproved = @Val WHERE ((@IsTopic=1 AND ReplyTo IS NULL) OR (@IsPost=1 AND ReplyTo>0)) AND TopicId IN(SELECT Id FROM @TBL_IDS);  
	
	-- Update forum counter
	IF @Val=1
		EXEC Forum_CalcLastPost @ForumId,@Topics,@Posts;
	ELSE
	BEGIN
		SET @Posts = (@Posts*-1)
		SET @Topics=(@Topics*-1)
		EXEC Forum_CalcLastPost @ForumId,@Topics,@Posts;
	END
	
	--Update Users totalposts	
	EXEC User_CalcTotalPostsByTopicIds @Ids;

	
END		    


GO

-- ----------------------------
-- Procedure structure for [Topic_Mod_ToggleLock]
-- ----------------------------
DROP PROCEDURE [Topic_Mod_ToggleLock]
GO

CREATE PROC [Topic_Mod_ToggleLock]
	@Ids VARCHAR(MAX),
	@Val BIT --1:Lock , 0: Unlock
AS
BEGIN
	SET NOCOUNT ON
	
	UPDATE Topics SET IsLocked = @Val WHERE ReplyTo IS NULL AND TopicId IN(SELECT ID FROM SplitStringToTable(@Ids));
	
END

GO

-- ----------------------------
-- Procedure structure for [Topic_Mod_ToggleSticky]
-- ----------------------------
DROP PROCEDURE [Topic_Mod_ToggleSticky]
GO

CREATE PROC [Topic_Mod_ToggleSticky]
	@Ids VARCHAR(MAX),
	@Priority SMALLINT,
	@Expires DATETIME
AS
BEGIN
		
	UPDATE Topics
	SET	 [Priority] = @Priority, PriorityExpires = @Expires 
	WHERE TopicId IN(SELECT Id FROM SplitStringToTable(@Ids))	
	
END

GO

-- ----------------------------
-- Procedure structure for [Topic_QuoteList]
-- ----------------------------
DROP PROCEDURE [Topic_QuoteList]
GO
CREATE PROC [Topic_QuoteList]
		@TopicId INT,
		@Ids VARCHAR(MAX)
	AS
BEGIN
	SET NOCOUNT ON
	SELECT TopicId,UserName,TopicName,[Message] FROM Topics WHERE ReplyTo=@TopicId AND TopicId IN (SELECT ID FROM SplitStringToTable(@Ids)) 
END
	

GO

-- ----------------------------
-- Procedure structure for [Topic_Save]
-- ----------------------------
DROP PROCEDURE [Topic_Save]
GO
CREATE PROC [Topic_Save]
					@TopicId INT,
					@TopicName NVARCHAR(200),
					@ForumId INT,												
					@Icon VARCHAR(20),
					@Message NTEXT,
					@ReplyTo INT,
					@Priority INT,
					@PriorityExpires DATETIME,
					@IsApproved BIT,					
					@EditedBy NVARCHAR(64),
					@Reason nvarchar(150),
					@RemoteUrl NVARCHAR(128),
					@UserName NVARCHAR(32),
					@UserId INT,
					@IP VARCHAR(20),
					@Tags NVARCHAR(136),
					@PollId INT,
					@IncreaseMemberPosts BIT,				
					@RETURN_VALUE INT =0 OUT
	AS	
	
	
	BEGIN		
		
		IF @TopicId <=0
		BEGIN
			
			INSERT INTO Topics(
									TopicName,
									ForumId,									
									Icon,
									[Message],
									ReplyTo,
									Priority,
									PriorityExpires,
									IsApproved,
									RemoteUrl,
									UserName,
									UserId,
									IP,
									Tags,
									LastEdited							
									
			)
					VALUES(
									@TopicName,
									@ForumId,									
									@Icon,
									@Message,
									@ReplyTo,
									@Priority,
									@PriorityExpires,
									@IsApproved,
									@RemoteUrl,
									@UserName,
									@UserId,
									@IP,
									@Tags,
									GETUTCDATE()								
									
					)			
				
					

			-- update last post
			IF @IsApproved=1
			BEGIN				
				--Update User Total Posts
				IF @IncreaseMemberPosts=1
					EXEC User_IncreasePosts @UserId,1;

				IF @ReplyTo Is NULL
					EXEC Forum_CalcLastPost @ForumId,1,0;
				ELSE
					EXEC Forum_CalcLastPost @ForumId,0,1;
			END
		
		SELECT @RETURN_VALUE=SCOPE_IDENTITY();	
					
						
		END		
		ELSE
			BEGIN
				UPDATE Topics 
					SET TopicName=@TopicName,
						Icon=@Icon,
						[Message]=@Message,
						Tags=@Tags,
						Priority=@Priority,
						PriorityExpires=@PriorityExpires,
						IsApproved =@IsApproved,
						LastEdited=GETUTCDATE(),
						EditedBy=@EditedBy,
						Reason=@Reason					
				WHERE TopicId=@TopicId
				
				SELECT @RETURN_VALUE=@TopicId;					
				
			END			
						
													
	END
		
	
	

GO

-- ----------------------------
-- Procedure structure for [Topic_SearchAdvanced]
-- ----------------------------
DROP PROCEDURE [Topic_SearchAdvanced]
GO

CREATE PROC [Topic_SearchAdvanced]
		@Kwd NVARCHAR(200),
		@Username VARCHAR(64),
		@SQL NVARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON	
	
	EXEC sp_executesql @SQL,N'@Kwd nvarchar(200), @Username VARCHAR(64)',@Kwd,@Username
END


GO

-- ----------------------------
-- Procedure structure for [Topic_SearchEngineSitemap]
-- ----------------------------
DROP PROCEDURE [Topic_SearchEngineSitemap]
GO

CREATE PROC [Topic_SearchEngineSitemap]		
		@PageIndex INT,
		@PageSize INT
AS
BEGIN
	SET NOCOUNT ON;
	
	WITH CTE AS
	(
		SELECT 
			ROW_NUMBER() OVER (ORDER BY TopicId) AS RowNum,
			TopicId,			
			TopicName,
			Created	
		FROM Topics WITH(NOLOCK)
		WHERE IsApproved=1 AND ReplyTo IS NULL
	)
	SELECT T.* FROM CTE T
	
	WHERE  T.RowNum BETWEEN (@PageIndex - 1) * @PageSize + 1 AND @PageIndex * @PageSize
	
	
END
		

GO

-- ----------------------------
-- Procedure structure for [Topic_SearchQuick]
-- ----------------------------
DROP PROCEDURE [Topic_SearchQuick]
GO

CREATE PROC [Topic_SearchQuick]
						@ForumId INT = 0,
						@IsModerator BIT,
						@Keyword NVARCHAR(200),
						@ResultType SMALLINT=0, -- 0: Topics,1: Posts
						@MaxResults INT =500
AS
BEGIN
	SET NOCOUNT ON;
	
	;WITH CTE AS
		(
			SELECT 			
				T.TopicId,	
				MAX(T2.TopicId) LastPostId,
				COUNT(T2.TopicId) Replies	
			FROM Topics T LEFT JOIN Topics T2 ON T.TopicId=T2.ReplyTo
			WHERE (@IsModerator=1 OR T.IsApproved=1)	
					AND (T.Tags LIKE N'%'+@Keyword+'%' OR T.TopicName LIKE N'%'+@Keyword+'%' OR T.[Message] LIKE N'%'+@Keyword+'%') 															
					AND (@ForumId<=0 OR T.ForumId=@ForumId OR (T.XForumId=@ForumId AND (T.RedirectExpires IS NULL OR T.RedirectExpires>GETUTCDATE())))		

			GROUP BY T.TopicId
		)
		SELECT TOP (@MaxResults)
			T.TopicId,
			T.TopicName,
			T.Icon,	
			T.ForumId,	
			T.xForumId,
			T.PollId,			
			T.IsLocked,
			T.IsApproved,			
			T.Priority,
			T.RemoteUrl,			
			T.UserName,
			T.UserId,
			T.Created,
			T.LastEdited,
			T.Views,
			C.Replies,
			C.LastPostId,
			T2.Created as LastPosted,
			T2.UserId as LastUserId,
			T2.UserName as LastUserName,
			F.ForumName
			
			FROM CTE C INNER JOIN Topics T ON C.TopicId=T.TopicId
					INNER JOIN Forums F ON T.ForumId=F.ForumId
					LEFT JOIN Topics T2 ON C.LastPostId=T2.TopicId
			WHERE (@ResultType=1 AND T.ReplyTo IS NULL) OR @ResultType=0
			ORDER BY LastPosted DESC					
				
		
END	

GO

-- ----------------------------
-- Procedure structure for [Topic_ToggleApproved]
-- ----------------------------
DROP PROCEDURE [Topic_ToggleApproved]
GO

CREATE PROC [Topic_ToggleApproved]
	@Id INT
AS
BEGIN
	UPDATE Topics SET IsApproved= IsApproved^1 WHERE TopicId=@Id
END

GO

-- ----------------------------
-- Procedure structure for [Topic_ToggleLock]
-- ----------------------------
DROP PROCEDURE [Topic_ToggleLock]
GO
	
CREATE PROC [Topic_ToggleLock]
	@Id INT
AS
BEGIN
	UPDATE Topics SET IsLocked= IsLocked^1 WHERE TopicId=@Id
END

GO

-- ----------------------------
-- Procedure structure for [User_CalcTotalLikes]
-- ----------------------------
DROP PROCEDURE [User_CalcTotalLikes]
GO
CREATE PROCEDURE [User_CalcTotalLikes]
@UserId INT
AS
BEGIN
SET NOCOUNT ON;
 UPDATE Users SET ThanksSent=(SELECT COUNT(1) FROM Likes L WITH(NOLOCK) WHERE L.FromId = @UserId),
				ThanksReceived=(SELECT COUNT(1) FROM Likes L WITH(NOLOCK) WHERE L.ToId=@UserId)
	WHERE UserId=@UserId; 
END

GO

-- ----------------------------
-- Procedure structure for [User_CalcTotalPosts]
-- ----------------------------
DROP PROCEDURE [User_CalcTotalPosts]
GO

CREATE PROC [User_CalcTotalPosts]
			@UserId INT
AS
BEGIN
	SET NOCOUNT ON;
	
	UPDATE Users SET TotalPosts=(SELECT COUNT(1) FROM Topics T WITH(NOLOCK) INNER JOIN Forums F ON T.ForumId=F.ForumId  
							WHERE T.IsApproved=1 
								AND F.IncreaseMemberPosts=1 
								AND T.UserId = @UserId)
	WHERE UserId=@UserId; 	
	
END

GO

-- ----------------------------
-- Procedure structure for [User_CalcTotalPostsByTopicIds]
-- ----------------------------
DROP PROCEDURE [User_CalcTotalPostsByTopicIds]
GO
CREATE PROCEDURE [User_CalcTotalPostsByTopicIds]
	@Ids VARCHAR(MAX)
AS
BEGIN
	DECLARE @TBL_IDS TABLE([Id] INT);
 	DECLARE @USER_IDS TABLE (Id INT);
	DECLARE @UserId INT;

	INSERT INTO @TBL_IDS(Id) SELECT TopicId FROM Topics T WITH(NOLOCK) WHERE T.TopicId IN (SELECT ID FROM SplitStringToTable(@Ids)) OR ReplyTo IN (SELECT ID FROM SplitStringToTable(@Ids)) ;
	
	INSERT INTO @USER_IDS(Id) SELECT DISTINCT UserId FROM Topics T WITH(NOLOCK) INNER JOIN @TBL_IDS T2 ON T.TopicId=T2.Id;

	DECLARE ROW_CURSOR CURSOR FOR SELECT Id FROM @USER_IDS;

	OPEN ROW_CURSOR 
	FETCH NEXT FROM ROW_CURSOR INTO @UserId
	WHILE @@FETCH_STATUS = 0
	BEGIN
		EXEC User_CalcTotalPosts @UserId;
		FETCH NEXT FROM ROW_CURSOR INTO @UserId          

	END

	CLOSE ROW_CURSOR

	DEALLOCATE ROW_CURSOR
END

GO

-- ----------------------------
-- Procedure structure for [User_ChangePassword]
-- ----------------------------
DROP PROCEDURE [User_ChangePassword]
GO

CREATE PROCEDURE [User_ChangePassword]
					@UserId INT,
					@OldPwd VARCHAR(136),
					@NewPwd VARCHAR(136),
					@RETURN_VALUE BIT =0 OUT
					
AS
BEGIN
	SET NOCOUNT ON;
	IF EXISTS(SELECT 1 FROM Users WHERE UserId=@UserId AND Pwd = @OldPwd)
		BEGIN
			UPDATE Users SET Pwd=@NewPwd WHERE UserId=@UserId
			SELECT @RETURN_VALUE=1
		END		
	ELSE
		SELECT @RETURN_VALUE=0
		
END



GO

-- ----------------------------
-- Procedure structure for [User_EmailExists]
-- ----------------------------
DROP PROCEDURE [User_EmailExists]
GO
CREATE PROC [User_EmailExists] 
			@Email VARCHAR(64),
			@RETURN_VALUE BIT =0 OUT
AS
	BEGIN
		SET NOCOUNT ON;
		
		IF EXISTS (SELECT 1 FROM Users WHERE Email=@Email)
			SELECT @RETURN_VALUE= 1;
		ELSE
			SELECT @RETURN_VALUE= 0;
			  		
	END


GO

-- ----------------------------
-- Procedure structure for [User_ExistsByUserName]
-- ----------------------------
DROP PROCEDURE [User_ExistsByUserName]
GO
CREATE PROCEDURE [User_ExistsByUserName] 
			@UserName NVARCHAR(64)=null,
			@RETURN_VALUE BIT =0 OUT
AS
	BEGIN
		SET NOCOUNT ON;
		
		IF EXISTS (SELECT UserName FROM Users WHERE UserName=@UserName)
			SELECT @RETURN_VALUE= 1;
		ELSE
			SELECT @RETURN_VALUE = 0;
			  		
	END


GO

-- ----------------------------
-- Procedure structure for [User_GetByActiveCode]
-- ----------------------------
DROP PROCEDURE [User_GetByActiveCode]
GO

CREATE PROC [User_GetByActiveCode]
		@ActiveCode VARCHAR(64),
		@Email VARCHAR(63)
AS
BEGIN
	SET NOCOUNT ON;
	IF EXISTS (SELECT 1 FROM Requests WITH(NOLOCK) WHERE (DATEADD(HH, 48, Created)> GETUTCDATE()) AND ActiveCode=@ActiveCode AND Email=@Email )
	BEGIN
		 UPDATE Users SET IsActived = 1 WHERE Email=@Email;
		 
		 DELETE FROM Requests WHERE (DATEADD(HH, 48, Created)< GETUTCDATE()) OR (ActiveCode=@ActiveCode AND Email=@Email) ;
		 
		 SELECT UserId, UserName,Pwd FROM Users WITH(NOLOCK) WHERE Email=@Email;
	END		
		
END

GO

-- ----------------------------
-- Procedure structure for [User_InBirthday]
-- ----------------------------
DROP PROCEDURE [User_InBirthday]
GO

CREATE PROC [User_InBirthday]
AS
	BEGIN
		SET NOCOUNT ON;
		SELECT U.UserName,U.Email,R.CssClass
		FROM Users U INNER JOIN Roles R ON U.RoleId=R.RoleId 
		WHERE U.Birthday IS NOT NULL AND (MONTH(U.Birthday)=MONTH(GETUTCDATE()) AND DAY(U.Birthday)=DAY(GETUTCDATE()))
	END

GO

-- ----------------------------
-- Procedure structure for [User_IncreasePosts]
-- ----------------------------
DROP PROCEDURE [User_IncreasePosts]
GO
CREATE PROCEDURE [User_IncreasePosts]
	@UserId INT ,
	@Increase INT
AS
BEGIN
	UPDATE Users SET TotalPosts=TotalPosts+@Increase WHERE UserId=@UserId;
END

GO

-- ----------------------------
-- Procedure structure for [User_Info]
-- ----------------------------
DROP PROCEDURE [User_Info]
GO
CREATE PROC [User_Info]
		@UserId INT	= 0  ,
		@UserNameOrEmail VARCHAR(63)=NULL
		
AS
BEGIN
	SET NOCOUNT ON;
	
	
IF @UserId >0 
	BEGIN
		 SELECT
				UserId,				
				UserName,	  		
				Email,
				FullName,
				[Signature],
				Avatar,
				RegDate,
				LastLogin,
				TotalVisits,
				TotalPosts,
				IsActived,
				Sex,
				Birthday,
				[Address],
				YahooId,
				SkypeId,
				MSN,
				AOL,
				Website,
				Blog,
				Gallery,
				Bio,
				Hobby,
				Note,
				RoleName,
				CssClass,
				R.RoleId		
				
			FROM
				Users U WITH(NOLOCK) INNER JOIN Roles R WITH (NOLOCK) ON U.RoleId=R.RoleId WHERE U.UserId=@UserId;
	END

	ELSE 
		BEGIN
			 SELECT U.*,R.RoleName,R.CssClass FROM 
				Users U WITH(NOLOCK) INNER JOIN Roles R WITH(NOLOCK)
				ON U.RoleId=R.RoleId WHERE (UserName=@UserNameOrEmail OR Email=@UserNameOrEmail)
		END
	
END

GO

-- ----------------------------
-- Procedure structure for [User_List]
-- ----------------------------
DROP PROCEDURE [User_List]
GO

CREATE PROC [User_List]
				@PageIndex INT=1,
				@PageSize INT = 25,
				@TotalRows INT=0 OUT
AS

BEGIN
	SET NOCOUNT ON
	
	SELECT @TotalRows=COUNT(1) FROM Users WITH (NOLOCK);
	 WITH CTE	
		 AS
			(
			SELECT 
				ROW_NUMBER() OVER (ORDER BY UserId ) AS RowNum,
				U.UserId,
				U.RoleId,
				U.UserName,
				U.Pwd,
				U.Email,
				U.FullName,	   				
				U.Avatar,
				U.RegDate,
				U.LastLogin,
				U.TotalVisits,
				U.TotalPosts,
				U.ThanksReceived,
				U.ThanksSent,
				U.IsActived,
				U.Sex,
				U.Birthday,
				U.[Address],
				U.YahooId,
				U.SkypeId,
				U.MSN,
				U.AOL,
				U.Website,
				U.Blog,
				U.Gallery				
					
			FROM  Users U WITH (NOLOCK)
			)
			
			SELECT * FROM CTE WHERE RowNum BETWEEN (@PageIndex - 1) * @PageSize + 1 AND @PageIndex * @PageSize;

	
END	

GO

-- ----------------------------
-- Procedure structure for [User_ListAll]
-- ----------------------------
DROP PROCEDURE [User_ListAll]
GO

CREATE PROC [User_ListAll]
AS
BEGIN
	SELECT * FROM Users
END

GO

-- ----------------------------
-- Procedure structure for [User_OnlineList]
-- ----------------------------
DROP PROCEDURE [User_OnlineList]
GO

CREATE PROCEDURE [User_OnlineList] 
						@PastMinutes INT
AS
	BEGIN

		SET NOCOUNT ON
		
		SELECT 
		U.UserName,R.CssClass
		FROM Users U  
		INNER JOIN Activities A ON A.UserId = U.UserId
		INNER JOIN Roles R  WITH(NOLOCK) ON U.RoleId=R.RoleId 		
		WHERE A.IsPrivated=0 AND (DATEADD(Mi, @PastMinutes, A.LastAction)> GETUTCDATE()) 
		AND A.UserID>0
		
	END

GO

-- ----------------------------
-- Procedure structure for [User_ResetPwd]
-- ----------------------------
DROP PROCEDURE [User_ResetPwd]
GO
CREATE PROC [User_ResetPwd]
				@UserId int,				
				@Pwd VARCHAR(90)
AS
	BEGIN
		UPDATE Users SET Pwd=@Pwd WHERE UserId=@UserId	
		
	END

GO

-- ----------------------------
-- Procedure structure for [User_SaveAvatar]
-- ----------------------------
DROP PROCEDURE [User_SaveAvatar]
GO
CREATE PROCEDURE [User_SaveAvatar]
@UserId INT,
@Avatar varchar(80)
AS
BEGIN
  UPDATE Users SET Avatar=@Avatar WHERE UserId=@UserId;
END

GO

-- ----------------------------
-- Procedure structure for [User_SaveEmailPwd]
-- ----------------------------
DROP PROCEDURE [User_SaveEmailPwd]
GO
CREATE PROCEDURE [User_SaveEmailPwd]
					@UserId INT,
					@OldPwd VARCHAR(136),					
					@NewPwd VARCHAR(136),
					@Email VARCHAR(64),
					@RETURN_VALUE BIT =0 OUT
					
AS
BEGIN
	SET NOCOUNT ON;
	IF EXISTS(SELECT 1 FROM Users WHERE UserId=@UserId AND Pwd = @OldPwd)
		BEGIN
			UPDATE Users SET Pwd= ISNULL(NULLIF(@NewPwd,''),Pwd),
			Email=@Email
			WHERE UserId=@UserId
			SELECT @RETURN_VALUE=1
		END		
	ELSE
		SELECT @RETURN_VALUE=0
		
END



GO

-- ----------------------------
-- Procedure structure for [User_SaveNote]
-- ----------------------------
DROP PROCEDURE [User_SaveNote]
GO
CREATE PROCEDURE [User_SaveNote]
@UserId INT,
@Note NVARCHAR(MAX)
AS
BEGIN

UPDATE Users SET Note=@Note WHERE UserId=@UserId;

END

GO

-- ----------------------------
-- Procedure structure for [User_SaveProfile]
-- ----------------------------
DROP PROCEDURE [User_SaveProfile]
GO
CREATE PROCEDURE [User_SaveProfile]
	@UserId INT,
	@FullName NVARCHAR(63),
	@Address NVARCHAR(128),
	@Sex BIT = NULL,
	@BirthDay DATETIME ,
	@Website NVARCHAR(128),
	@Blog NVARCHAR(128),
	@YahooId NVARCHAR(36),
	@SkypeId NVARCHAR(36),
	@MSN NVARCHAR(36),
	@AOL NVARCHAR(36),
	@Bio NVARCHAR(136),
	@Hobby NVARCHAR(136)
AS
BEGIN
	UPDATE Users SET 
		FullName=@FullName,
		[ADDRESS]=@Address,
		Birthday = @BirthDay,
		Sex=@Sex,
		Website=@Website,
		Blog=@Blog,
		YahooId = @YahooId,
		SkypeId = @SkypeId,
		MSN = @MSN,
		AOL = @AOL,
		Bio=@Bio,
		Hobby=@Hobby
	WHERE UserId=@UserId
END

GO

-- ----------------------------
-- Procedure structure for [User_SaveSignature]
-- ----------------------------
DROP PROCEDURE [User_SaveSignature]
GO

CREATE PROC [User_SaveSignature]
		@UserId INT,
		@Signature NVARCHAR(MAX)
AS
BEGIN
	UPDATE Users
	SET
		SIGNATURE=@Signature WHERE UserId=@UserId
END 

GO

-- ----------------------------
-- Procedure structure for [User_ToggleLock]
-- ----------------------------
DROP PROCEDURE [User_ToggleLock]
GO

	
CREATE PROC [User_ToggleLock]
	@Id INT
AS
BEGIN
	UPDATE Users SET IsBanned=IsBanned^1 WHERE UserId=@Id; 
END


GO

-- ----------------------------
-- Function structure for [fn_diagramobjects]
-- ----------------------------
DROP FUNCTION [fn_diagramobjects]
GO

	CREATE FUNCTION [fn_diagramobjects]() 
	RETURNS int
	WITH EXECUTE AS N'dbo'
	AS
	BEGIN
		declare @id_upgraddiagrams		int
		declare @id_sysdiagrams			int
		declare @id_helpdiagrams		int
		declare @id_helpdiagramdefinition	int
		declare @id_creatediagram	int
		declare @id_renamediagram	int
		declare @id_alterdiagram 	int 
		declare @id_dropdiagram		int
		declare @InstalledObjects	int

		select @InstalledObjects = 0

		select 	@id_upgraddiagrams = object_id(N'dbo.sp_upgraddiagrams'),
			@id_sysdiagrams = object_id(N'dbo.sysdiagrams'),
			@id_helpdiagrams = object_id(N'dbo.sp_helpdiagrams'),
			@id_helpdiagramdefinition = object_id(N'dbo.sp_helpdiagramdefinition'),
			@id_creatediagram = object_id(N'dbo.sp_creatediagram'),
			@id_renamediagram = object_id(N'dbo.sp_renamediagram'),
			@id_alterdiagram = object_id(N'dbo.sp_alterdiagram'), 
			@id_dropdiagram = object_id(N'dbo.sp_dropdiagram')

		if @id_upgraddiagrams is not null
			select @InstalledObjects = @InstalledObjects + 1
		if @id_sysdiagrams is not null
			select @InstalledObjects = @InstalledObjects + 2
		if @id_helpdiagrams is not null
			select @InstalledObjects = @InstalledObjects + 4
		if @id_helpdiagramdefinition is not null
			select @InstalledObjects = @InstalledObjects + 8
		if @id_creatediagram is not null
			select @InstalledObjects = @InstalledObjects + 16
		if @id_renamediagram is not null
			select @InstalledObjects = @InstalledObjects + 32
		if @id_alterdiagram  is not null
			select @InstalledObjects = @InstalledObjects + 64
		if @id_dropdiagram is not null
			select @InstalledObjects = @InstalledObjects + 128
		
		return @InstalledObjects 
	END
	

GO

-- ----------------------------
-- Function structure for [SplitStringToTable]
-- ----------------------------
DROP FUNCTION [SplitStringToTable]
GO

CREATE FUNCTION [SplitStringToTable]
(
 @String VARCHAR(MAX) 
)
RETURNS @otTemp TABLE([ID] VARCHAR(20))
AS
BEGIN
	DECLARE @sTemp VARCHAR(10)
	DECLARE @Delimiter CHAR(1)=','
	SET @String=REPLACE(@String,';',',');
	WHILE LEN(@String) > 0
	 BEGIN
	  SET @sTemp = LEFT(@String, ISNULL(NULLIF(CHARINDEX(@Delimiter, @String) - 1, -1),LEN(@String)))
	  SET @String = SUBSTRING(@String,ISNULL(NULLIF(CHARINDEX(@Delimiter, @String), 0), LEN(@String)) + 1, LEN(@String))
	  INSERT INTO @otTemp VALUES (@sTemp)
	 END

RETURN
END

GO

-- ----------------------------
-- Indexes structure for table Activities
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [Activities]
-- ----------------------------
ALTER TABLE [Activities] ADD PRIMARY KEY ([SessionId])
GO

-- ----------------------------
-- Indexes structure for table AttachFiles
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [AttachFiles]
-- ----------------------------
ALTER TABLE [AttachFiles] ADD PRIMARY KEY ([Id])
GO

-- ----------------------------
-- Indexes structure for table EmailQueue
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [EmailQueue]
-- ----------------------------
ALTER TABLE [EmailQueue] ADD PRIMARY KEY ([Id])
GO

-- ----------------------------
-- Indexes structure for table Forums
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [Forums]
-- ----------------------------
ALTER TABLE [Forums] ADD PRIMARY KEY ([ForumId])
GO

-- ----------------------------
-- Indexes structure for table IPLocks
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [IPLocks]
-- ----------------------------
ALTER TABLE [IPLocks] ADD PRIMARY KEY ([Id])
GO

-- ----------------------------
-- Indexes structure for table Moderators
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [Moderators]
-- ----------------------------
ALTER TABLE [Moderators] ADD PRIMARY KEY ([Id])
GO

-- ----------------------------
-- Indexes structure for table Notifications
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [Notifications]
-- ----------------------------
ALTER TABLE [Notifications] ADD PRIMARY KEY ([Id])
GO

-- ----------------------------
-- Indexes structure for table Polls
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [Polls]
-- ----------------------------
ALTER TABLE [Polls] ADD PRIMARY KEY ([PollId])
GO

-- ----------------------------
-- Indexes structure for table Ratings
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [Ratings]
-- ----------------------------
ALTER TABLE [Ratings] ADD PRIMARY KEY ([Id])
GO

-- ----------------------------
-- Indexes structure for table Reports
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [Reports]
-- ----------------------------
ALTER TABLE [Reports] ADD PRIMARY KEY ([Id])
GO

-- ----------------------------
-- Indexes structure for table Requests
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [Requests]
-- ----------------------------
ALTER TABLE [Requests] ADD PRIMARY KEY ([Id])
GO

-- ----------------------------
-- Indexes structure for table Roles
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [Roles]
-- ----------------------------
ALTER TABLE [Roles] ADD PRIMARY KEY ([RoleId])
GO

-- ----------------------------
-- Indexes structure for table Subscriptions
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [Subscriptions]
-- ----------------------------
ALTER TABLE [Subscriptions] ADD PRIMARY KEY ([Id])
GO

-- ----------------------------
-- Indexes structure for table Tags
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [Tags]
-- ----------------------------
ALTER TABLE [Tags] ADD PRIMARY KEY ([Id])
GO

-- ----------------------------
-- Indexes structure for table Topics
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [Topics]
-- ----------------------------
ALTER TABLE [Topics] ADD PRIMARY KEY ([TopicId])
GO

-- ----------------------------
-- Indexes structure for table Users
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [Users]
-- ----------------------------
ALTER TABLE [Users] ADD PRIMARY KEY ([UserId])
GO

-- ----------------------------
-- Indexes structure for table Votes
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table [Votes]
-- ----------------------------
ALTER TABLE [Votes] ADD PRIMARY KEY ([Id])
GO

-- ----------------------------
-- Foreign Key structure for table [Likes]
-- ----------------------------
ALTER TABLE [Likes] ADD FOREIGN KEY ([FromId]) REFERENCES [Users] ([UserId]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [Moderators]
-- ----------------------------
ALTER TABLE [Moderators] ADD FOREIGN KEY ([ForumId]) REFERENCES [Forums] ([ForumId]) ON DELETE CASCADE ON UPDATE NO ACTION
GO

-- ----------------------------
-- Foreign Key structure for table [Users]
-- ----------------------------
ALTER TABLE [Users] ADD FOREIGN KEY ([RoleId]) REFERENCES [Roles] ([RoleId]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO
